1

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 process1 and process2, and a machine can only have one process. Both processes share a lot of attributes so setting up one table seemed logical.

However process2 has a lot of extra data, and even sub-categorizes itself with process2a, 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 process1 to 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.

  • Design the right model first, and then worry about auditing, change tracking, etc. The model is much more important than worrying about having to create an additional trigger to track data changes. – Aaron Bertrand Sep 9 '14 at 19:47
  • 6
    Also, as a general rule, don't be so afraid of things like NULLs and EAV. Not everything fits into the classroom relational theory or 42nd normal form. – Aaron Bertrand Sep 9 '14 at 19:48
  • thank you Aaron. I think that is my problem. I am trying to stick to a classroom model. Which would you rather go with. More of an EAV approach or NULLS? – jmzagorski Sep 9 '14 at 19:53
  • I'm not sure I'd go for either, and I honestly don't know enough about your model (both current and future) to know for sure what is optimal. Just don't keep those options off the table just because you don't like them. Both EAV and NULLs definitely have their place, just like cursors, triggers, dynamic SQL, etc. etc. – Aaron Bertrand Sep 9 '14 at 19:55
  • 1
    @Neil meh, I disagree. Nothing is all bad, all the time, for all use cases. There are plenty of ways to work around any downside of EAV you could illustrate coherently. – Aaron Bertrand Sep 9 '14 at 20:23
1

Treat the various processes as sub-types. There will then be entity process_base, which contains all the common attributes, process1, process2, process2a etc. for the process-specific attributes.

Implement these as a table each. A view which combines them all together may simplify usage

create view process_all as
select <whatever>
from process_base
inner join process1 <etc>

union all

select <whatever>
from process_base
inner join process2 <etc>
...

This way you minimise the NULL columns (if that is desirable to you) but maintain the unity of "process" as a single idea.

  • That is what I ended up doing . My code was already setup with the abstract base and specialized classes so it made sense to break up the tables for the main processes. The sub processes only had 1 to 3 columns so I kept them in the main process table for now. I appreciate everyone's help! – jmzagorski Sep 14 '14 at 13:48
1

Which fields will you need to do any of the following on?

  • sorting
  • filtering
  • counting
  • grouping
  • type checking

If there are some fields that you simply want to store for viewing or what-not, then you don't need to put them in their own columns. You could store them as JSON or XML or some other serialized form.

But if you need to do the above on your fields, then you almost certainly want real database columns for them. And by real database columns I mean not EAV or LOBs.

If it sounds like a lot of work, then consider using Hibernate (with inheritance) to generate your database schema for you. And use Hibernate Envers to handle change capturing.

  • I was considering the XML data type, especially since some configurations may be added for our different locations, that are specific to those locations only. I just never used it before so I was not sure. – jmzagorski Sep 9 '14 at 20:15
  • as for the filtering/sorting. It will always be through the primary key. Basically the user enters their location, the machine (the PK) and the rest of the columns are just there for them to maintain and print out on the form – jmzagorski Sep 9 '14 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.