4 ms·
> Include an Hardware Asset FK in your Link M2M table. Model each binary link explicitly, so a Y cable = 2-3 different Links that point to the same cable Asset.
by perlgeek 4y ago
> Include an Hardware Asset FK in your Link M2M table. Model each binary link explicitly, so a Y cable = 2-3 different Links that point to the same cable Asset. Or you can have single Links with M2M inputs/outputs. But definitely don't model Y cables explicitly.
Our cables have stickers with cable IDs, which are stored in the CMDB (and can be used to trace the endpoints through patch fields, for example).
So if you model a Y cable as two physical cables, you have to both allow duplicate cable IDs AND multiple cables per port. Both of these have significant downsides in preventing data entry errors.
- 7thaccount 4y agoCan't you just have 2 tables? One for 2 end and one for 3 ends?
- wintermute2dot0 4y agoOr just have a cable type column (which could be a pointer/foreign key back to a table of cable types, so you can add the cable with 4 ends that comes out next year).
- 7thaccount 4y agoYeah that sounds even better and what I typically see in production databases
- pphysch 4y ago> So if you model a Y cable as two physical cables No. Model it as two abstract "Physical Links" or "Connections" which FK to the same physical cable Asset, which is uniquely determined by its inventory ID and/or manufacturer model+serial. If a Link needs to know whether its part of a Y cable Asset, it can join the Asset table. If a Y cable Asset needs to know what it's connected to, it can join the Link table. Additionally, you have a nice clean Link table for doing graph traversal (get me everything connected to this impacted Asset by degree <= 2), and a nice clean Asset table for inventory tracking.