From: "Alexandr Savinov" Newsgroups: comp.databases.theory References: Subject: Re: Question about modeling Date: Thu, 27 Jan 2005 11:43:23 +0100 Lines: 53 Organization: FhG.AiS.KD X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original Message-ID: <41f8c5d8$1@news.fhg.de> Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!news-fra1.dfn.de!news-koe1.dfn.de!usenet-feed.fhg.de!news.fhg.de!not-for-mail Xref: dp-news.maxwell.syr.edu comp.databases.theory:29612 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é" schrieb im Newsbeitrag news:CZtJd.30725$Qb.29245@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. >