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

After having a walk and thinking about this some more, the best I've
come up with is to do a kind of 'linked list' approach, where
'pointers' to previous and next rows are stored, rather than indexes.

After having a walk and thinking about this some more, the best I've
come up with is to do a kind of 'linked list' approach, where
'pointers' to previous and next rows are stored, rather than indexes.

After having a walk and thinking about this some more, the best I've
come up with is to do a kind of 'linked list' approach, where
'pointers' to previous and next rows are stored, rather than indexes.

On Apr 29, 2:11 pm, Rik Hemsley <rik... (AT) gmail (DOT) com> wrote:
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

On Apr 29, 2:11 pm, Rik Hemsley <rik... (AT) gmail (DOT) com> wrote:
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

On Apr 29, 2:11 pm, Rik Hemsley <rik... (AT) gmail (DOT) com> wrote:
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

On 29.04.2008 14:11, Rik Hemsley wrote:
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.

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.

I find this design spot on - at least from the description we have so far.

You should probably give a little more detail.

Kind regards

robert

On 29.04.2008 14:11, Rik Hemsley wrote:
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.

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.

I find this design spot on - at least from the description we have so far.

You should probably give a little more detail.

Kind regards

robert

On 29.04.2008 14:11, Rik Hemsley wrote:
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.

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.

I find this design spot on - at least from the description we have so far.

You should probably give a little more detail.

Kind regards

robert