I've been asked to create a database to track and report testing progress of a large number of IO points for an industrial plant. I can't seem to come up with an elegant way to split up the data, so I'd appreciate input from folks with more experience.
The IO points come in five "flavors" (sorry I can't go into detail; NDA issues). Every IO point has a certain number of fields in common -- port, channel, things like that. Additionally, each flavor has its own attributes which are irrelevant for all the others. So type A has port, channel, and foo; type B has port, channel, and bar.
They're all going into the same report, and I'd like to be able to sort just by port and channel, ignoring flavor. So on one hand, cramming everything into one table with fields for port, channel, foo, and bar, and then just leaving 9/10ths of the 'bar' values blank, is appealing. It would be easy to add new IO points and maintain unique port/channel combinations.
The downside is that there are so many flavor-specific fields that if I display a blank space in the report for every field that doesn't apply to a given flavor, it's going to get very crowded and very ugly. I might be able to hack something together in VB to hide the irrelevant fields, but it seems like that would be difficult to edit and maintain.
One alternative would be to put each flavor into its own table, and then have a report that pulls data from all 5. It seems like the queries involved would get messy and I'm not sure how much freedom I would have with sorting/displaying the final list of items. I'm not wild about having to use subforms for data entry either, but it's doable.
I've also considered putting the core data (port/channel) into one table, and then having five tables for the details -- one that only contains foo, one that only contains bar, etc. But in my extremely limited experience, this seems like a pretty unintuitive way to organize a database.
From a design/standards perspective, which would be the least-worst option? I'm leaving this project right around the time this database will start to see heavy use, so I want to make things as straightforward as possible for my successor.