Records in a Series - How to query?

Question

Records in a Series - How to query?

somnium7

Old Hand

Points: 347
More actions
February 16, 2007 at 7:34 am

#118939

Hello,
I am building a DB to handle production materials for my 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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

I am not too certain why you are complicating youself by using self join table. Do you have to build using only one table that hold the series of dies? If you are allowed to use more than one table, why don't you separate the information as follows?

Create two tables as folllows:

Table SeriesHeader that has columns SeriesID_PK and NumSeries.
Table SeriesDetail that has SeriesID_FK, Sequence, and Color.

NumSeries is total number in the series and Sequence is the sequence in the series. By the table name you can see the relationship between them. 1 to many between SeriesHeader and SeriesDetail. Then create index on SeriesID_PK for SeriesHeader table and SeriesID_FK for SeriesDetail table in case the tables getting bigger.

I suggest this way because it's easier to understand and debug later. If you have parent and child relation into one table, once the parent information get into trouble it's very hard to debug.

How to use:

Select SeriesHeader.SeriesID_PK, SeriesHeader.NumSeries, SeriesDetail.Sequence, SeriesDetail.Color From SeriesHeader Join SeriesDetail ON (SeriesHeader.SeriesID_PK = SeriesDetail.SeriesID_FK) Where SeriesHeader.SeriesID_PK = @SeriesID Order By SeriesDetail.Sequence

Please enlighten me if I am missing any.

somnium7 Old Hand Points: 347 More actions · Answer 2

Hi Terry,

I have done an experiment with the self join method and the result was good. It really doesn't seem any more complicated either. e.g.

SELECT D2.uniqueID, D2.series_number, D2.status

FROM Dies D1 JOIN Dies D2

ON D1.uniqueID = D2.parentID

WHERE D1.uniqueID = '1A72D5'

ORDER BY D2.series_number

result:

1A72D514IN-STOCK

98F3EB24IN-STOCK

971D9934IN-STOCK

93B5B044IN-STOCK

Here I have the desired hierarchical relationship by just adding one new column to the table (parentID). I do see what you mean about trouble with parent information. If the first die in the series gets broken or wears out prematurely I can completely orphan the rest of the series if the parent record is deleted. The safest thing here would be to transfer the uniqueID and part number of the old die to the new die.

In your example above I do not see a way to connect the series relationship with the actual die records. Would I be adding seriesID into my Dies table?

How would I enforce integrity with your method? I imagine I wouldn't want to use Cascade at all. Some tips would be helpful here as I am still a bit of a caveman with DB design.

Thanks!

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 3

I am sorry that I could not understand clearly your situation. What is dies table? In your original inquiry it said:

I am building a DB to handle production materials for my 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.

So is the dies table equal to the production material table? I am having difficulty to understand your saying "I do not see a way to connect the series relationship with the actual die records". If my assumtion is correct, I suggest the following:

Product or Dies table has ItemID_PK.
Table SeriesDetail amended to have ItemID_FK, SeriesID_FK, Sequence, and Color(or Status). Combination of ItemID_FK and SeriesID_FK is unique in the table SeriesDetail.

Then the following is how to use:

Select Product.ItemID_PK, SeriesHeader.SeriesID_PK, SeriesHeader.NumSeries, SeriesDetail.Sequence, SeriesDetail.Color From SeriesHeader Join SeriesDetail ON (SeriesHeader.SeriesID_PK = SeriesDetail.SeriesID_FK) Join Product ON (SeriesDetail.ItemID_FK = Product.ItemID_PK) Where SeriesHeader.SeriesID_PK = @SeriesID Order By SeriesDetail.Sequence

Please advise if this make sense.