On Apr 29, 2:11 pm, Rik Hemsley <rik...@[EMAIL PROTECTED]
> 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.
> * 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
Not sure I understand, but would the following view describe
partcodes?
create table X (
ProductID int not null,
PartCode char(1) not null,
primary key (ProductID, PartCode)
);
create view PartCodes as
select ProductID, PartCode,
row_number() over (
partition by ProductID
order by PartCode
) as SequenceNumber
from X;
/Lennart
|
