Re: Question about modeling

Mathieu Pagé wrote:
> Hi, I have this model:
>
> DEPARTMENTS (dp_id, cl_id, dp_otherStuff)
> CLIENT (cl_id, cl_otherStuff)
> CONTRACT (cn_id, cn_details)
> DEPTS_CONTRACT_LINK (dp_id, cn_id)
>
> 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 ?

What you really want here is a constraint like:

CHECK (NOT EXISTS
(SELECT dc.cn_id
FROM depts_contract_link dc
JOIN departments d ON d.dp_id = dc.dp_id GROUP BY dc.cn_id
HAVING COUNT(DISTINCT d.cl_id) > 1
));

But I don't suppose Access supports anything so complex. This is probably something that you will unfortunately have to enforce via application code. Received on Tue Jan 25 2005 - 17:18:36 CET