On 29.04.2008 14:11, Rik Hemsley wrote:
> 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.
What constraint do you want to check with the check constraint? Is it
possible that you just want a unique constraint on (prodId, seqNum)?
Note also that if the sequence number is only needed for ordered
retrieval values do not need to be consecutive.
> * It's difficult to update while the check constraint is active.
Not sure what you mean by this. A constraint that prevents any updates
is either defined wrongly or the updates are forbidden.
Depending on DB product you may be able to defer the constraint
evaluation to commit time.
> Is it possible to improve this design?
I find this design spot on - at least from the description we have so far.
> 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.
You should probably give a little more detail.
Kind regards
robert
|
