Self Join or Intersect?

[Ruberto]

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!

[Twey]

I don't like the way you've done that (14, 33, 25, &c.). You'd have a much easier time storing the number and series separately, and then you could do a simple select.

[Ruberto]

The only place the user ever sees this is on the Record Add form where it's displayed with two lists:

    <select name="first">
      <option value="0">0</option>
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
      <option value="6">6</option>
      <option value="7">7</option>
      <option value="8">8</option>
      <option value="9">9</option>
    </select> OF     <select name="last">
      <option value="0">0</option>
      <option value="1">1</option>
      <option value="2">2</option>
      <option value="3">3</option>
      <option value="4">4</option>
      <option value="5">5</option>
      <option value="6">6</option>
      <option value="7">7</option>
      <option value="8">8</option>
      <option value="9">9</option>
    </select>

After the record is created the user is never exposed to the series number value in the DB again except when the application returns their new part number which ends with the series number. The reason *I like* this method is because later on the part number is encoded into a fixed-length bar code. I'm allowed 2 characters and this works fine. Besides that it makes my ORDER BY clause simpler for several queries.
I'm sorry if you have a problem with my constraints.

[Twey]

Besides that it makes my ORDER BY clause simpler for several queries.

But surely it's not too much effort to ORDER BY series, number; instead of ORDER BY series.

It's not a serious problem, I just think you're making it a lot harder for yourself than you have to.