In my database, I have items, which reflect attributes on documents being filled out by users. Each value given in a document, e.g. you select a certain option for a field or select a check-box, becomes an item/attribute in my table.
Such attributes could be: Smoker, Non-Smoker, Region (Europe, USA, ...), Hair-Color
In the tables, this roughly looks like the following:
Document ID | Name 1 | doc-1 2 | doc-2 3 | doc-3 Attribute ID | Name 1 | Smoker 2 | Non-Smoker 3 | Region-Europe 4 | Region-USA 5 | Hair-Brown 6 | Hair-Blond Item ID | Document | Attribute 1 | 1 | 1 2 | 1 | 4 3 | 2 | 2 4 | 2 | 3 5 | 2 | 5 6 | 3 | 2 7 | 3 | 6
To offer search possibilities, it shall be possible to allow the users for building generic queries. For example, I would like to find the documents, which have the following attributes:
(Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond)
(would result in document #1 being found)
How can I execute such a query in the most efficient way, and maybe use EF-core and linq-to-sql to push that down to SQL? How could I actually query for that in plan SQL the most efficient way?
I can do it quite easy in memory, but since my database contains 100k+ items, this might get slow pretty soon.
Thank you for any help on this!
Update: Related questions on SO
- Database Design for Tagging