Re: data modeling question

Brandon Metcalf wrote:
> I asked a question similar to this a couple of weeks ago, but the
> requirement has changed a bit and I want to be sure I'm designing my
> tables correctly.
>
> I have the following table:
>
> CREATE TABLE workorder (
> number VARCHAR(8),
> quantity INTEGER,
> generic BOOLEAN,
>
> PRIMARY KEY (number)
> );
>
> If generic is true, number will need to be associated with at least
> one other number in the same table. I need to ensure the integrity of
> this association. So, I'm thinking a second table:
>
> CREATE TABLE generic (
> gnumber VARCHAR(8),
> number VARCHAR(8),
>
> PRIMARY KEY (gnumber, number),
>
> FOREIGN KEY (gnumber)
> REFERENCES workorder(number)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
>
> FOREIGN KEY (number)
> REFERENCES workorder(number)
> ON DELETE RESTRICT
> ON UPDATE CASCADE
> );
>
> Any better way of doing this?
>

I think that will work. There might be one alternative you could look at. Add a parent field to workorder and drop generic all together. BUT that would only let any workorder have one parent. Not sure if you need to have a workorder point back to multiple parents. Also it makes query'ing out a little harder. (Personally I think having the second table makes queries easier)

If you do keep the generic table, I was not sure at first what the fields meant, the naming was a little confusing. I'd recommend names like:

orignumber and altnumber or assocnumber or something.

-Andy