Self-Referencing, Lookup Table - And it's companion Lookup Property Table
This was originally posted in OopTeamSize.
There were some additional comments posted pertaining to the structure below but were mostly asking for additional information. I have removed those comments; I figured 'starting over' a good thing. I would look to assert that this was a schema I inherited and had to live with for five years. For various reasons, I was unable to modify it in any manner. Here's a rudimentary data dictionary of the self-referencing lookup table.
tblLookUp
field..datatype.......notes
key....BIGINT.........Primary Key - auto increment
type...BIGINT.........Foreign Key - references tblLookup.key
desc...VARCHAR(256)...The description/value/whatever of the lookup entity (e.g., Country Code)
And, here's a rudimentary data dictionary of the property table. I know it's really not properly formed as it has a composite primary key.
tblLookUpProperties
field....datatype.......notes
key......BIGINT.........Primary Key/Foreign Key - references tblLookUp.Key
name.....VARCHAR(64)....Primary Key - the name of the property (e.g., Country Name)
value....VARCHAR(512)...Value associated with the property (e.g., United States)
The purpose of the tables was to allow for nearly unmitigated growth of 'simple' business entities within the database without the need to modify the schema or even the generic stored procedures (e.g., uspLookUpSelectListByType) used to retrieve and manipulate the data.
There is a lot more I can say about the above, but I figured it might be good to just have an open discussion (if warranted) on it.
[ScottNeumann]
It looks like a form of Entity-Attribute-Value model; an approach that rarely has a valid justification. See http://en.wikipedia.org/wiki/Entity-Attribute-Value_model -- DaveVoorhis
Seems what is really wanted is DynamicRelational. --top