Sql-server – Best way to Design Key,value pairs in relational database

sql server

I have a table which has a list of all attributes that any object can have:

attr_id    attr_name    attr_data_type
------      -----        --------------

I have a table [object_key] in which each object_key_id has an unique list i.e set of attr_id,attr_value pairs.

object_key_id   attr_id   attr_value
---------       -------   ----------
1               1          'value1'
1               2          'value2'
2               1          'value3'
2               2          'value4'
3               1          'value1'
3               2          'value2'
3               3          'value3'

In this case, what is the best way to get the object_key_id for a given set of attr_id,attr_value pairs.

Ex: Input: [{1, 'value1'}, {2, 'value2'}]
Output: 1

What's the best way to build a unique index i.e to have unique object_key_id for a set of attr_id,attr_value pairs?

Best Answer

What you want is called (exact) Relational Division.

See also this article: Divided We Stand: The SQL of Relational Division

There are several ways to have a query like that solves this problem. You can load the input data to a (temporary) table or use a CTE like this:

WITH input_data (attr_id, attr_value) AS
  ( SELECT *
    FROM 
      ( VALUES                              -- the input data. 
          (1, 'value1'), 
          (2, 'value2')
      ) AS i (attr_id, attr_value)
  ),
t AS                                        -- all the object_key_id values.
  ( SELECT DISTINCT object_key_id           -- if you have a separate table with those
    FROM tableX                             -- you can use it, instead. 
  )
SELECT object_key_id
FROM t
WHERE NOT EXISTS                            -- this subquery ensures
      ( SELECT attr_id, attr_value          -- that all input attribute/value pairs
        FROM input_data                     -- appear for an object_key
      EXCEPT
        SELECT attr_id, attr_value
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id 
      ) 
  AND NOT EXISTS                            -- and this subquery does the reverse
       ( SELECT attr_id, attr_value         -- i.e. the exact part of the division
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id 
      EXCEPT
        SELECT attr_id, attr_value
        FROM input_data
      ) ;

Test at SQL-Fiddle

This question at StackOverflow: How to filter SQL results in a has-many-through relation has a few more ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.

Related Question