Post reply

Records in a Series - How to query?

  • 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?

  • February 18, 2007 at 5:59 pm

    #690386

    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:

    1. Table SeriesHeader that has columns SeriesID_PK and NumSeries.
    2. 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.

     

     

  • February 21, 2007 at 8:43 am

    #690932

    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:

    1A72D5 14 IN-STOCK

    98F3EB 24 IN-STOCK

    971D99 34 IN-STOCK

    93B5B0 44 IN-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!

  • February 21, 2007 at 2:41 pm

    #691030

    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:

    1. Product or Dies table has ItemID_PK.

    2. 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.

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

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