Re: Question about modeling

From: David Cressey <david.cressey_at_earthlink.net>
Date: Wed, 02 Feb 2005 14:43:24 GMT
Message-ID: <gG5Md.7108$Ix.6743_at_newsread3.news.atl.earthlink.net>


"Mathieu Pagé" <mathieu_dot_page_at_GoogleEmailSystem.com> wrote in message news:rvhLd.12660$Mx.506574_at_wagner.videotron.net...
> David Cressey wrote:
> > Mathieu,
> >
> > Let me see if I can express your requirements in the simplest terms.
> >
> > A client can have contracts.
> > A client can have departments.
> > A contract can be divided into portions.
> > A department can have a portion of a contract.
> > Two departments can have different portions of the same contract if and
only
> > if the two departments belong to the same client.
> >
> > Is this an accurate summary what you are trying to model?
> >
> >
> >
>
> Hi David,
>
> I'm sorry for the delay.
>
> your assumptions are corrects.
>
> Mathieu Pagé

OK, thanks, now let me ask if the following set of tables covers the subject. Please DON'T consider whether these are "too inefficient" just yet. Just whether they are over constrained or underconstrained with regard to the data to be stored.

First, there are four tables, called CLIENTS, CONTRACTS, DEPARTMENTS, and PORTIONS, with primary key CLIENT_ID, CONTRACT_ID, DEPARTMENT_ID, and PORTION_ID. All other columns in these tables are dependent data that describe the four entities tabulated.

DEPARTMENT_ID and PORTION_ID are "invented data", not provided by the enterprise. The enterprise might identify departments by saying "the sales department of client 12345" or something like that. It probably doesn't identify portions at all. It just says something like "the sales department of client 12345 has 40% of the 67890 contract." Please bear with me. Invented data may be removed from the design at a later stage. Right now, I need it just to understand what we are talking about (right, Gene?).

There are four more tables:

CLIENT_DEPARTMENTS with key CLIENT_ID, DEPARTMENT_ID. CLIENT_CONTRACTS with key CLIENT_ID, CONTRACT_ID. CONTRACT_PORTIONS with key CONTRACT_ID, PORTION_ID. DEPARTMENT_PORTIONS with key DEPARTMENT_ID, PORTION_ID.

These columns, together, are primary keys for their tables. They are also foriegn keys back to the first four tables. Any other columns in these tables are dependent data describing the relationship.

Now the constraint can be expressed as

CHECK CLIENT_DEPARTMENTS.CLIENT_ID = CLIENT_CONTRACTS.CLIENT_ID in (select *

    from

      CLIENT_DEPARTMENTS,
      CLIENT_CONTRACTS,
      CONTRACT_PORTIONS,
      DEPARTMENT_PORTIONS
    where
      CLIENT_DEPARTMENTS.DEPARTMENT_ID = DEPARTMENT_PORTIONS.DEPARTMENT_ID
    and DEPARTMENT_PORTIONS.PORTION_ID = CONTRACT_PORTIONS.PORTION_ID     and CONTRACT_PORTIONS.CONTRACT_ID = CLIENT_CONTRACTS.CONTRACT_ID     )

I apologize for the use of non-standard SQL. I also apologize for the length of the above. It's not easy to read. All I'm trying to do is translate the requirement into slightly more formal language, before actually designing a solution.

If the above is logically correct, then we can proceed onward to try to design something workable. If not, let's sort out the logical errors before proceeeding.

So, Mathieu, does this look right to you, from a logical perspective? Received on Wed Feb 02 2005 - 15:43:24 CET

Original text of this message