Обсуждение: data modeling question

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?

--
Brandon

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
genericall together.   BUT that would only let any workorder have one parent.  Not sure if you need to have a workorder
pointback to multiple parents.  Also it makes query'ing out a little harder.  (Personally I think having the second
tablemakes 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'drecommend names like: 

orignumber and altnumber or assocnumber or something.

-Andy