Records with extra properties: sparse table or EAV?

Question

I have a model that already has a couple dozen of columns that will be filled most of the time. Now I need to add fields that might be different each time.

what's the best approach? I don't like the EAV pattern. I don't like the idea of having a sparse table either, especially considering how these extra properties could be very different.

Example:

WorkOrder:
PK id
FK assigned_to
FK contractor
DATE expected_completion
DATE actual_completion
... (many more)

Now I want to add properties like:

ep_1 (extra_property)
ep_2
ep_3
ep_4
... (many more)

These extra properties can be wildly different from record to record, and most of the time there will be a limited number of them, but there are no guarantees.

Think of records as:

id  |  assigned_to  | contractor  | ... | ep_1   | ep_2  | ep_3 | ... | ep_n
1   |  2            | 3           | ... | XYZ    | NULL  | NULL | ... | 23
2   |  3            | 5           | ... | NULL   | 1     | NULL | ... | NULL
3   |  2            | 1           | ... | NULL   | 0     | NULL | ... | NULL
4   |  4            | 1           | ... | XYZ    | NUL   | NULL | ... | 45

I want to be able to list, filter, and search records as if those extra properties were actually columns, eg: I should be able to make queries like SELECT fields FROM table WHERE ep_n > 20 and SELECT fields FROM table WHERE ep_1='ABC'

What's the best solution to this?

Answer 1

What database? With SQL Server for instance, you can consider using Sparse Columns which are optimized for sparse tables. For EAV modeling I recommend reading the whitepaper on the subject from the SQL Server customer adviser team: Best Practices for Semantic Data Modeling for Performance and Scalability. Many of the recommendations apply to other vendors too, are not SQL Server specific.