Design question: List of mixed record types

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Design question: List of mixed record types

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2009-01-14 : 08:57:04

I'm not sure how best to approach this design task, so I'm hoping someone can offer some advice.

I have two tables, let's call them Lists and ListDetails. Lists is the parent, ListDetails is the child.

Three other tables contain (let's say) Vegetables, Presidents, and Theories. Doesn't matter, they're just three tables with completely different types of records. All three tables have an identity PK column called Rec_Num.

A List will include any combination of Vegetables, Presidents, and Theories, in a specific order. ListDetails will store references to those three tables and a Sort_Order value.

The question is, what is the best way to design ListDetails to accommodate references to one of three different tables? Obviously I'll have to join these tables later. Would it be better to do something like this:

Record_Rec_Num int NOT NULL,
Record_Type int (or varchar(1)) NOT NULL,
Sort_Order int NOT NULL

Or this:

Vegetable_Rec_Num int NULL,
President_Rec_Num int NULL,
Theory_Rec_Num int NULL,
Sort_Order int NOT NULL

Or some other option? I can see potential pitfalls of either approach (I'm not sure how useful FKs will be in either scenario), and unfortunately I don't have unlimited time to test the two ideas. Does anyone have any advice as to what would be the best approach?

Thanks!
Ron Moses

ron2112
Starting Member

44 Posts

Posted - 2009-01-14 : 09:43:04

In attempting to answer my own question, I'm considering what the SELECT would look like if I needed to see the items in a given list. Using the first option, I believe I would need to do the following:


SELECT (CASE ListDetails.Record_Type
           WHEN 'V' THEN Vegetables.Name
           WHEN 'P' THEN Presidents.Name
           WHEN 'T' THEN Theories.Name
        END) AS Item_Name
FROM ListDetails
LEFT JOIN Vegetables
ON ListDetails.Record_Type = 'V'
   AND ListDetails.Record_Rec_Num = Vegetables.Rec_Num
LEFT JOIN Presidents
ON ListDetails.Record_Type = 'P'
   AND ListDetails.Record_Rec_Num = Presidents.Rec_Num
LEFT JOIN Theories
ON ListDetails.Record_Type = 'T'
   AND ListDetails.Record_Rec_Num = Theories.Rec_Num
WHERE ListDetails.List_Rec_Num = [value]
ORDER BY ListDetails.Sort_Order

Whereas the second option would require me to do more or less the same exact thing, only using three different fields in ListDetails. So I don't think the SELECT is what's going to make or break this plan.

Perhaps my larger concern should be data integrity. I don't see a way to enforce integrity using FKs in either instance, so I think I'm looking at triggers either way. Looks like a wash on integrity.

So I'm inclined to go with the first option for simplicity. If anyone can see an error in my logic, please correct me. I'd be grateful for any insights.

Thanks!
Ron Moses

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources