Re: ER diagram question

Hello

I had the same comment about circular database designs at my old school. The basic problem is the fact that it takes up extra hard drive space through having redundant keys, leading to a bigger file size.

Yet, at the very same school this was contradicted in a different module.

I have, as a circular design:

One to Many between Customer and Projects ie Customer.CustID being primary key with Projects.CustID being foreign key

One to Many between Projects and Communications ie Projects.ProjectID being primary key with Communications.ProjectID being the foreign key

One to Many between Customer and Communications ie Customer.CustID being primary key with Communications.CustID being the foreign key

You may want a primary key just for each communication, ie without a corresponding foreign key in another table.

I don't know enough about your business rules but I would've thought that a communication can have more than one topic (that's how I'd model it). If this were the case you would have a join table between Projects and Communications

Hope this helps

Peter Mount
info_at_petermount.au.com

"Jonck van der Kogel" <jonck_at_vanderkogel.net> wrote in message news:28a69e7c.0401191448.5dbfa676_at_posting.google.com...
> Hi everybody,
> It's been a while since I followed that course in college on database
> theory and my ER diagram skills are a bit rusty, I was hoping some of
> you might be willing to help me out.
>
> I'm setting up a database, where, among others, I have the following
> entities:
> Projects, Customers and Communications
>
> A customer can have multiple projects and a project can have only 1
> customer, so there's a 1:N relationship between customers and
> projects. This is not the thing that's troubling me, it's the
> communications (being a synopsis of any communication between the
> company and the customer about the project, can be anything from a
> phone call to an email ).
> A project can have many communications and a communciation will have
> as topic 1 project, so there's a 1:N relationship between projects and
> communications. But a customer can also have many communications and a
> communication is always originating from one customer, thus there is
> also a 1:N relationship between customers and communications, forming
> a circle in my ER diagram.
> I can remember vaguely that for some reason you don't want to have
> circular references in your ER diagram (is this correct? If so, could
> someone refresh my memory why this is so?) so my question is, how
> would I solve this?
> Or should I just let the communications table have a layout something
> like:
> | communication_id (PK) | project_id (FK) | customer_id (FK) |
> communication | ?
>
> Thanks for any help, Jonck
Received on Sat Jan 24 2004 - 11:51:20 CET