My colleague and I are having some trouble coming up with a database design, and I am doing my best to stay away from EAV for now.
We have an entity called
Preparation that stores configuration data for machines. There are two types of processes per machine, let's call these
process2, and a machine can only have one process. Both processes share a lot of attributes so setting up one table seemed logical.
process2 has a lot of extra data, and even sub-categorizes itself with
processes2b that is specific to those sub categories (
process1 only has one extra attribute for now). So that one table will have nulls in columns that are not specific to its process.
Splitting the table for each process was another option. However, we need to preserve any data change and splitting the tables will bring more work (most all transactions will be
INSERTS with a timestamp into a history table, followed by an
UPDATE on the live table if they are updating a record). For example, if a machine changes from
process2, then a record will have to be inserted in the history table from
process1, deleted from
process1 and then inserted into
process2. We are using the standard edition of SQL Server, so no CDC to help with logging changes.
I am trying to keep these pretty relational and I would hate to create one table with expected NULLS (maybe create
SPARSE columns if the NULL percentage is high?).
I am hoping someone has experience with this type of situation. Thank you.