Re: Question about modeling

David Cressey wrote:
> "mathmoi" <mathieu.page_at_gmail.com> wrote in message
> news:1107461386.603251.160360_at_c13g2000cwb.googlegroups.com...
>
> > First, i'd like to thank you for your help. It is _really_
appreciated.
>
> Thank you for your kind words. I haven't contributed anything to the
> solution yet.
> All I'm doing at this stage is clarifying the requirements.

But you do take of your time to read and aswer my post. This is what is appreciated.

> > I read your post... twice and it seem to me that you got it right.
You
> > defined the problem well.
>
> Thanks again. But I'm not satsified yey that I've defined the
problem well.
> The fact that I suggested 8 tables
> (4 for entities and 4 for relationships) while your solution has only
4
> tables suggests to me that I still need to clarify the requirements
some
> more.
>
> In particular, my last suggestion was probably too flexible.
>
>
>
> > I have implemented a solution (That, I must admit, do not look nice
to
> > me). Here is what I actualy have:
>
> > CLIENTS (CLIENTS_ID*, ...)
> > DEPARTMENTS (DEPARTMENTS_ID, CLIENT_ID (FK), ...)
> > CONTRACTS (CONTRACTS_ID, CLIENT_ID (FK), ...)
>
> > Then I have this table doing a link between DEPARTEMENTS AND
CONTRACT
>
> > CONTRACTS_DEPARTEMENTS(CONTRACTS_ID*, DEPARTEMENTS_ID*, CLIENT_ID
(FK
> > of both DEPARTMENTS and CONTRACTS))
>
> > So ! Now I have the contracts attached to the client and I also use
> > this FK in the relation with the DEPARTEMENTS table. So I can not
have
> > a contract shared among departments of different clients. What I do
not
> > like in this solution is that I duplicated the fact that a contract
> > belong to wich client. There is now two way to look it out : 1)
Look at
> > the CLIENT_ID attribute of CONTRACTS, 2) Follow a link from a
contact,
> > to a departments, to a client. But, maybe it is the thing to do and
I
> > should not worry at all.
>
> > In any case, I would like to know what is your solution.
>
> I believe, although I'm not sure, that your solution is as good as
the one I
> will come up with when I finish clarifying the requirements.
>
> > Again, thanks for your help and I hope I am clear, english is not
my
> > primary language and I fear I'm a better english reader than
writer.
>
> No problem at all! If your primary language is French, then
according to
> my mother, your primary language is quite a bit more precise than
mine is.
> In particular, I think the French word "Informatique" conveys a
much more
> precise concept than the English phrase "Information Technology".
But we'll
> do the best we can with English.
>
> If you would like to pursue this further, my next step was going to
be to
> model the department as a sub-entity of the client.
> The main effect of this would be to reduce the number of tables from
8 to 7,
> and to enforce the rule that
>
> "A client may have several departments, but a department can belong
to
> exactly one client."
>
> Your solution permits, if I have read it correctly, that a department
could
> exist without any client. Of course, such a department could never
> participate in any contracts, but maybe it's good to forbid such a
> department to begin with.

In fact a departement can not exist without a client. I'll try to enumerate all the rules that the model must enforce:

 o Each departments belongs to a client
 o Each client have one or more departments [1,N]

 o Each contract belongs to one or more departements [1,N]  o Those departments must all belong to a single client

I think there is no other rules to be enforced. At least I hope.

> Is this worth pursuing?

If you have a better solution and/or one that do not introduce redundancies, like my actual solution do, i'm certainly interested.

Again, thanks for you help.

Mathieu Pagé
informatique_at_csf_REMOVE_inc.com Received on Fri Feb 04 2005 - 17:36:10 CET