Self Referencing Lookup Table

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