Ruberto
02-16-2007, 03:45 PM
I am not a DBA. Normally I try to stay out of DB development and stick to my comfortable world of interfaces and business logic. Unfortunately, there isn't a DBA where I'm working so I've been sucked in. Here's the issue...
I am building a DB to handle production materials for the company. Some items are in a series though e.g. printing dies. For any particular job the dies required may be in a series and each one is used for a separate color. So in the table for dies there is a column of bit type to indicate the record is in a series. The next column holds the series number (1 of 4, 3 of 3, etc.) with the actual values being like 14, 33, 25.
So now I need a way to query the table in such a way as to return all records for a particular series. I see two ways of doing it and I'm not sure which is best.
The first method would be to add a new column to hold the ParentID, which would be the ID of the first record of the series. I could then use a self join on the table and return all records that are in a series and have the same ParentID and ordered by series number.
The next option is to use an intersect table to hold the IDs of all related records. Each record in this table would be the combination of ParentID and ChildID.
Which method is best and why? What benefits are there for one method over another? Is there an even better way to do this that I failed to think about?
Thanks in advance!
I am building a DB to handle production materials for the company. Some items are in a series though e.g. printing dies. For any particular job the dies required may be in a series and each one is used for a separate color. So in the table for dies there is a column of bit type to indicate the record is in a series. The next column holds the series number (1 of 4, 3 of 3, etc.) with the actual values being like 14, 33, 25.
So now I need a way to query the table in such a way as to return all records for a particular series. I see two ways of doing it and I'm not sure which is best.
The first method would be to add a new column to hold the ParentID, which would be the ID of the first record of the series. I could then use a self join on the table and return all records that are in a series and have the same ParentID and ordered by series number.
The next option is to use an intersect table to hold the IDs of all related records. Each record in this table would be the combination of ParentID and ChildID.
Which method is best and why? What benefits are there for one method over another? Is there an even better way to do this that I failed to think about?
Thanks in advance!