I am trying to improve the design of a table and was hoping this might
be the correct group in which to ask for help.
The table is called PartCodes and has three columns:
ProductID, PartCode, SequenceNumber
Each product may have multiple codes. Each code has a sequence number.
So, for example, the data contained might look like:
1, 'x', 1
1, 'y', 2
1, 'z', 3
2, 'a', 1
2, 'b', 2
3, 'c', 1
The problems with this table design:
* It's difficult to write the check constraint for SequenceNumber.
* It's difficult to update while the check constraint is active.
Is it possible to improve this design?
I would guess that this is a fairly common problem, but I'm having
trouble finding the words which will find me an answer through
searching the web and usenet.
Any help much appreciated!
Cheers,
Rik
|
