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:
- Date (range)
- Dropdownvalue (single)
- Dropdownvalues (multiple)
Based on that, I created the following database model:
- 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.