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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
