I am designing a database which implements self-describing data. this has a "data_def" table which defines all my various data elements, e.g.:
data_id description ======= =========== 1 customers 2 employees 3 locations
etc. I then have another table which defines all the possible values of each of those data sets, e.g.
data_id data_value_id description ======= ============= =========== 1 1 acme industries 1 2 acme widgets inc. 2 1 bob smith 2 2 john doe 2 3 robert bobert 3 1 planet earth 3 2 europe 3 3 poland 3 4 zurich
etc. Now as you can imagine, within each "data_id" the list of possible values is hierarchical, i.e. data_id=2 is a list of employees, and obviously an employee structure has parent-child relationships. Similarly data_id = 3 would have a hierarchy, e.g. Zurich would be under Switzerland under Europe under Planet Earth.
If I defined data_id as type INT and data_value_id as type herarchyid, will SQL Server "realise" that for each data_id, the data_value_id values form separate hierarchies, which are totally separate by data_id? Or does the hierarchy relationship apply across the entire table even if the unique clusterd index / primary key is data_id followed by data_value_id?
