Question about modeling

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. Received on Tue Jan 25 2005 - 16:55:14 CET