Re: Question about modeling

Once for each Contrat there is only one Client then you might store a reference to this client directly in Contract:

CONTRACT (cn_id, cl_id, cn_details)

Here the second field is a reference to a single CLIENT.

Then you remove this reference from Departments so you get:

DEPARTMENTS (dp_id, dp_otherStuff)

Now departments do not reference directly any client. Instead, departments are related to clients only indirectly via DEPTS_CONTRACT_LINK and then CONTRACT.

alex

--
http://conceptoriented.com



"Mathieu Pagé" <informatique_remove_this__at_csfinc.com> schrieb im Newsbeitrag
news:CZtJd.30725$Qb.29245_at_edtnps89...


> Hi, I have this model:


>

> DEPARTMENTS (dp_id, cl_id, dp_otherStuff)

> CLIENT (cl_id, cl_otherStuff)

> CONTRACT (cn_id, cn_details)

>

> Then I want to link DEPARTMENTS to CLIENT as each department can have 0..N

> contracts and each contract can be associated to 1..N departments, so I

> created this table:

>

> DEPTS_CONTRACT_LINK (dp_id, cn_id)

>

> So, it works :)

>

> Now I want to add this constraint : As I have said each contract can be

> associated with one or more departments, but I want that all those

> departments belongs to the same client. In other words, departments can

> share contract, but clients can not.

>

> How can I do this ?

>

> Mathieu Pagé

>

> PS : I will eventually use Ms access as a RDBS, so the solution can not

> use trigger to verify the constraint.

>