Referential Intergrity on tables with compound key

[winshent]

I am trying to enforce referential integrity on tables where a compound key is implemented.

I want to enforce integrity on the DETAIL table so that it can only use an SOR_ID from the SOR table that has a corresponding PhaseID in the HEADER table. Here is my current diagram:

The only solution i can think of at the moment is to build two queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a relationship between the two queries.

There must be a much nicer way of doing this though?

 

[Guus2005]

You could disconnect the link between Phase and SOR and then add a second Phase table to the diagram which will be named Phase_1 and connect it to SOR.

HTH

 

[winshent]

Hi, thanks for your quick response..

I dont think that will get me any closer to what i want to achieve.

For example, the records in the image below exist SOR. I want the database to restrict a record with PhaseID=64001 and SOR_ID=10A200 being added to DETAIL as there is no such record in SOR.

 

[Guus2005]

It would help if you could post a sample database.

I will try to explain. you have to make a cartesian product where you exclude all records that already exist in table SOR.

That's it.

Question: how can a SOR_ID exists in DETAIL but not in SOR? This compromises the referential integrity.

 

[winshent]

Guus2005

RE: "Question: how can a SOR_ID exists in DETAIL but not in SOR? This compromises the referential integrity"

Thats what i want to avoid.

I have attached the database with some sample data.

View attachment Relationships.zip

 

[Guus2005]

Remove the PhaseID from the SOR or from the Header. In a normalised database there is only one place for this field. I can't tell you where. It depends on what it means and where it belongs to. It would be my choice to remove it from the SOR table, but it seems that it is also responsible for the costs of whatever it is.

In short you have to look at the design of your database. It doesn't seem right.

 

[winshent]

Guus2005

Hi Guus

Thanks for your help.

I cant remove PhaseID as its a key field. It has to stay in the SOR table as the costs of the SOR items are Phase specific.

The database contains data for a property repair/ maintenance project. PhaseID is the phase of the project.

The HEADER contains the address of the property.
The DETAIL contains the list of works to be actioned on a property.
The SOR contains a list of works across the Phases of a project.

So a property in Phase 64000 that requires SOR item '10A210' will be charged £75.00/M². But if the property is in Phase 64001 then it will be charged at £77.00/M² for the same work item.

 

[Guus2005]

The SOR table is dedicated to SOR only(!) items. Costs is not for SOR only. It depends on Phase as well. That's why it doesn't belong in that table.

So you need a separate table where you connect SOR and Phase. In this table there must be a field called Costs. Seems to me the only fields are SOR_ID, Phase_ID and Costs.

Look at Wikipedia: Normalisation

 

[winshent]

Hi Guus

I know normalisation. I've been developing for 5 years...

As far as i can see, your solution doesnt help me achieve anything.. or is it that i don't understand it..

Could you make the changes to the file I uploaded and then re-upload. It will help me better to understand.

 

[georgedwilkinson]

It wouldn't be a bad idea to take Guus' advice and check out the Wikipedia entry for normalisation. Even those of us with over 20 years developing can use a refresher.

While there, check out the article on surrogate keys. I believe an understanding of that topic will help you to solve one of your problems (the topic of this thread).

In short, you need to change your primary key on SOR from the compound key to a new artificial (surrogate) key. Make the current compound key into a unique key. Any place you need to reference the unique record, use the new surrogate key in your joins/FKs instead of the old compound key.

It is very difficult to give you advice since the table names convey no meaning as to what the system is supposed to do. This design seems to have some flaws that prevent me from modifying it within my time constraints. I'll assume that is because you have sent out a "test" database to protect your IP.

Please let us know if you need additional help resolving the compound key issue.

 

[Guus2005]

Hi Guus

I know normalisation. I've been developing for 5 years...

As far as i can see, your solution doesnt help me achieve anything.. or is it that i don't understand it..

Could you make the changes to the file I uploaded and then re-upload. It will help me better to understand.

Hey, didn't wanna sound disrespectfull. I have been developing for 20+ years. Sometimes normalising is not so easy.

I always normalise up to the BCNF.
Boyce-Codd normal form

    Main article: Boyce-Codd normal form

A table is in Boyce-Codd normal form (BCNF) if and only if, for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

I think your design doesn't match these specifications. Can't help you there. i don't know the meaning of every attribute.