My question is for designing something in MSSQL server.
I work at an engineering company that designs embedded systems. Basically, we have 3 different hardware components in a product: one is for communication on a network (GPIB, Ethernet, Modbus...), one is for measurement/control functions, and the third is for miscellaneous stuff (like diagnostics and logging). Each piece of hardware has multiple firmware versions available. Each firmware has a set of registers used for configuration and a set of defaults.
I want to create a database to track the firmware, hardware, default configs, and register maps. I can create three separate tables for firmware, like
miscHardware, and then create tables that relate to these (i.e.,
commFirmware with a foreign key to
commDefaults with a FK to
Or... I can structure my tables so that there's one table for hardware, one for firmware, one for register maps, one for default configs, and one for hardware role (comm/misc/control).
The representation will be the same no matter which option I choose, so I don't have to shoehorn one design to fit the other. Right now, I'm leaning toward option 1, for two reasons.
- If I confuse something, it might not work - if I try to download
comm firmware 01onto a control board, it'll be blatantly screwed up. I don't think it is physically possible to do so - the processors are different.
- If I confuse something, it might actually work... for a while. Downloading the miscellaneous registers onto a communications board can be done, but it'll screw up communications ("Oh, you wanted to suppress overflow alarms? Too bad, here's a new MAC Address.")
In writing this, I've almost convinced myself, but I want to get a second opinion. Is the first approach superior in a way I'm missing? I'm looking for problems that could cause:
- Obvious performance issues
- Data integrity problems
- Maintenance nightmares
- Software development headaches
- Angry future coworkers visiting my house with hatchets, torches, and/or pitchforks in hand.