Efficient Query for Advanced EAV Model in SQL Server

eavsql servert-sql

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure.

Today, a product table already exists, containing 4 fixed fields. We want to upgrade the system, allowing an infinite amount of additional product fields, defined by the manufacturer of each product. We call these additional fields 'parameters'.

A parameter can be of the following data type:

Text
Date (range)
Boolean
Dropdownvalue (single)
Dropdownvalues (multiple)

Based on that, I created the following database model:

Database Scheme
:

Product: the original product table, containing the fixed fields.
Parameter: this table defines all types of parameters. Currently 5: Text, Date, Boolean, Dropdown value (single), Dropdown value (multiple)
ManufacturerParameter: this table stores which parameters are defined by the product manufacturer (Manufacturer table not included in the picture) for all of his products.
ProductParameter: this is where the actual product data of all the defined parameters is being stored. A parameter of the type 'Text', stores its information in the field 'Text', 'Date' in 'DatumBegin' and 'DatumEnd', Bit/Boolean in 'Boolean'.
ParameterValueListItem: this table contains the pre-defined values of the Dropdownlist values for both single and muliple coupling. These are the values you get to see in the Dropdownlist when creating or altering a product.
ProductParameterValueListItem: this is where the actual product data is stored for the Dropdownlist values.

I need to build a webpage in ASP.NET C# which shows a list of all product data, including each defined parameter with it's value for that product. I need to be able to filter the list by searching on any field/parameter. This list also needs to be sortable on any field/parameter.

I tried to filter the list by outer joining Product – ProductParameter – ProductParametervalueListItem – ParameterValueListItem to obtain a table result containing all data, and then filtering that list by using a dynamic where clause, built and passed through by the web app. This works when you search on 1 parameter but when you start searching on multiple parameters, you get incorrect results because each parameter is a different line in the table result and there simply is no 'AND'-match between parameter A and B, because they don't exist in the same record (line).

Can anyone advice me how this can be achieved? The most perfect solution would be 1 table result, containing all data and each parameters being shown as a column in that same product row. Is this too complex to handle at database level?

Thanks for reading this far. Any advice is welcome.

Best Answer

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is nothing wrong with applications running CREATE TABLE now and again.

Second, I am not sure I understand why you have normalised Parameter into its own table? Why not put that directly into the ManufacturerParameter table.

Third, if you insist on proceeding with your current model, there are ways to achieve what you want (at least if I am interpreting your requirement correctly). What you can do is to write your query in such a way that it is a sums up the search argument when there is a match and then use HAVING to filter out the values that match. I am assuming that only one of the fields Text, Boolean, Datum etc are populated per ProductParameter record (you probably want to enforce this with a constraint)

For example, to search for all products that have a bolean = true for one parameter AND text = 'abc' for some other parameter you can do:

SELECT P.Name
FROM Product P
JOIN ProductParameter PP
WHERE P.ID = Foo
  AND PP.Boolean = 1 OR PP.Text = 'abc'  ... /* For each filter */
GROUP BY P.Name /* And any other things you want out of product */
HAVING COUNT(*) >= [Number of where clauses]

If you need to list all the parameters of this product, you can use the above query template as a nested query and join back to ProductParameter.

The above query CAN be optimised by maintaining a computed column in ProductParameter that has a string representation of the different data types in that table. That way, the above OR statements can be rewritten as an IN list (which you will want to pass as a table valued parameter).

I would like to repeat that what you are doing is probably very wrong. If you do it, you will most likely need to hand tune most of your query plans - the optimiser will not help you anymore. And that is assuming you don't have too many query variants, which will run your plan cache full.

Related Solutions

Field types database design best practice

Not discounting @gbn's suggestion (in a comment on the question) to possibly store this data somewhere other than in a RDBMS, I will say that if you do decide to go the RDBMS route, you are better off using a single table with a column per each "type" such that they can be strongly-typed. Or, you could use a single table with a single string field as all data can be converted/serialized to a string, but then you have to be careful to do the conversion properly in both directions for all operations, and that might prove problematic if some uses of that table either incorrectly convert or forget to convert altogether.

The reason for choosing a single table is mostly logistics: it is far easier to dynamically select a different column based on a COALESCE or CASE (or CASE-like) clause than it is to dynamically pull from different tables in a query based on the TypeID of the row (at least not without having to LEFT JOIN all "type" tables in all queries (yikes!).

Depending on which RDBMS you are using, there might even be vendor-specific features to assist in this model. For example, SQL Server, starting in version 2008, I believe, has a SPARSE option that can be added to NULLable columns. This allows for a NULL value to take up 0 bytes whereas normally, for fixed-length fields (i.e. pretty much all of them that aren't [N]VAR{something} or {something} VARYING or XML or generically text or SQL_VARIANT), they would always take up their specified number of bytes. The downside is that for non-NULL values, they now take up 4 extra bytes per row. But it can be a huge savings when the column is 75% or more NULL across all rows. This feature was designed specifically to address this type of model.

Just make sure to have a CHECK CONSTRAINT to ensure that for all of the value_ fields, either none, or only one, of them is NOT NULL.

Then you can access via something like:

COALESCE(value_int, value_string, value_datetime, etc.)

And again, if you are using SQL Server (and maybe one or more other vendors have this feature), you can make use of "filtered indexes" to index each specific value_ field where that value is not null:

CREATE NONCLUSTERED INDEX [IX_FieldValues_ValueInt]
  ON dbo.[FieldValues] ([ValueInt])
  WHERE [ValueInt] IS NOT NULL;

This will allow you to target rows that do have values, although probably not when using the generic COALESCE access method, but when targeting a specific Value% field.

AND, looking again at the two options, I would actually go with Option 3, which is a single table. In Option 2 it does not seem like there is much purpose in breaking field_type_id out as that data is not really a parent to the field_value data. A field_id can only have a single value, right? So just put field_type_id in the field_value table. And make use of it in the CHECK CONSTRAINT that ensures a single field at most is NOT NULL, to ensure that the one field, if any, that is NOT NULL matches the field_type_id.

SQL Server – Most Efficient Way to Generate a Diff

Here is another approach:

SELECT
  di.name,
  di.date,
  x.field,
  x.oldValue,
  x.newValue
FROM
  @diffInput AS di
  LEFT JOIN dbo.myTable AS mt ON
    mt.version = @version
    AND mt.name = di.name
    AND mt.date = di.date
  CROSS APPLY
  (
    SELECT
      'fieldA',
      mt.fieldA,
      di.fieldA
    WHERE
      NOT EXISTS (SELECT mt.fieldA INTERSECT SELECT di.fieldA)

    UNION ALL

    SELECT
      'fieldB',
      mt.fieldB,
      di.fieldB
    WHERE
      NOT EXISTS (SELECT mt.fieldB INTERSECT SELECT di.fieldB)

    UNION ALL

    SELECT
      'fieldC',
      mt.fieldC,
      di.fieldC
    WHERE
      NOT EXISTS (SELECT mt.fieldC INTERSECT SELECT di.fieldC)

    UNION ALL

    ...
  ) AS x (field, oldValue, newValue)
;

This is how it works:

The two tables are joined using an outer join, @diffInput being on the outer side to match your right join.
The result of the join is conditionally unpivoted using CROSS APPLY, where "conditionally" means that each pair of columns is tested individually and returned only if the columns differ.
The pattern of each test condition
```
NOT EXISTS (SELECT oldValue INTERSECT SELECT newValue)
```
is equivalent to your
```
oldValue != newValue
OR (oldValue IS NULL AND newValue IS NOT NULL)
OR (oldValue IS NOT NULL AND newValue IS NULL)
```
only more concise. You can read more about this use of INTERSECT in detail in Paul White's article Undocumented Query Plans: Equality Comparisons.

On a different note, since you are saying,

Both the input table and existing table are expected to be quite large over time

you may want to consider replacing the table variable you are using for the input table with a temporary table. There is a very comprehensive answer by Martin Smith that explores differences between the two:

What's the difference between a temp table and table variable in SQL Server?

In short, certain properties of table variables, like e.g. absence of column statistics, may make them less query optimiser-friendly for your scenario than temporary tables.

Best Answer

Related Solutions

Field types database design best practice

SQL Server – Most Efficient Way to Generate a Diff

Related Question