Design of Common Tables (Attribute/Property tables, etc)
-
What's your opinion on designing common tables? For example, when you have a generic key-value pair record attribute table, do you create one attribute table for each primary table? Do you add a "type" column with a foreign key to a separate "type" table and filter records using a join? Do you create an indexed "code" column and filter using known codes (could optionally point to a look-up table for better documentation
? Or, do you use multiple indexed bit/boolean columns and filter using these?I recognize the pros/cons of each alternative. I'm just wondering what others use and why.
Example: (*Pseudo-SQL)
TABLE [record1] (record1ID int, title varchar(64));
TABLE [record2] (record2ID int, title varchar(64));
TABLE [prop] (propID int, typeCode int, recordID int, key varchar(64), value varchar(1024));
INSERT [record1] (1, '1-one');
INSERT [record1] (2, '1-two');
INSERT [record2] (1, '2-one');
INSERT [record2] (2, '2-two');
INSERT [prop] (1, 100, 1, '1-myKey-one', '1-myValue-one');
INSERT [prop] (2, 200, 2, '2-myKey-two', '2-myValue-two');
INSERT [prop] (3, 200, 1, '2-myKey-one', '2-myValue-one');
-
Don Peterson, an informed and interesting semi-regular SQL Server Central writer, wrote this article (which I happen to agree with): http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
This, and the 198 "Your Opinion" entries, will probably give you a good sense of what people are thinking. (He wrote this in July 2004; the most recent post was March 2007.)
Philip
-
mixing different types in the same table is really just the EAV model, which makes me ill.
Here's an amusing commentary on the EAV which I agree with:
http://weblogs.sqlteam.com/davidm/articles/12117.aspx
---------------------------------------
elsasoft.org
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply