Changing Status

[Seamus Hand]

I am trying to write a component maintenance application and have
tables as follows

Part

Part #
Part Description
Active (y/N)

as these parts are obsoleted, ie Active changes to N, then I also have
a
Replacement table

Replacement

Part #
Replacement Part Number (FK from Part with Active =Y)

My problem is that if the status of Active in the Part Table
subsequently goes to N then the data in Replacement table is invalid.
How do I represent this dependence ? Because Active is only Y/N then I
cannot use it is as a secondary key . Any ideas ?

Thanks
Seamus

[Bob Quintal]

Re: Changing Status

seamushand@hotm ail.com (Seamus Hand) wrote in
news:3aa33ae2.0 311101539.69866 612@posting.goo gle.com:
[color=blue]
> I am trying to write a component maintenance application and
> have tables as follows
>
> Part
>
> Part #
> Part Description
> Active (y/N)
>
> as these parts are obsoleted, ie Active changes to N, then I
> also have a
> Replacement table
>
> Replacement
>
> Part #
> Replacement Part Number (FK from Part with Active =Y)
>
> My problem is that if the status of Active in the Part Table
> subsequently goes to N then the data in Replacement table is
> invalid. How do I represent this dependence ? Because Active
> is only Y/N then I cannot use it is as a secondary key . Any
> ideas ?
>
> Thanks
> Seamus
>[/color]
You've given me some food for thought on my parts management
database.

You could constrain the Part Table such that Active must be yes if
there are children in the replacement table. This would mean that
the children must be deleted before setting the flag to no.

You could cause cascading deletes to remove the alternates when you
set Active to No.

Or you could process the replacement table recursively until you
hit a still active part.

As to your secondary key issue, it should be set on part # and
active, not active alone.

HTH
Bob