Re: some design questions

Steven,

If you want to enforce the rule that a contact may only be associated with a warehouse that is owned by the customer that the contact works for, you will need the customer_id column in your m:n table between contact and warehouse. I'm assuming that both contact and warehouse tables have the customer_id as part of the PK so this should happen by default.

If you allow contacts to move between customers, it becomes more tricky. I would suggest leaving contact the way you have it and adding a person table to allow a person to be a contact at one or more customers over time.

In case you hadn't already considered it, over time it may become neccessary to 'retire' some contacts or warehouses. You may not want to delete them, since there could be other historical info associated with them. You could use a datestamp to record when the record became invalid or maybe just a status code if the date is not important.

Regards, David.

Steven Tolleneer wrote:

> "Alan" <alanshein_at_erols.spambuster.com> schreef in bericht
> news:8ivrmi$iaa$1_at_bob.news.rcn.net...
> > You need three tables:
> >
> > Customer, Contacts, Warehouses
> >
> > Customer_ID is in each table.
> >
> > If you need to know which contact is in which warehouse, then put
 Warehouse
> > ID in the Contacts Table, or use the next method:
> >
> > If a contact can be at more than one warehouse, then you need a m:n
> > connecting table:
> >
> > Contacts_Warehouses
> >
> > which contains the Contact_ID and the Warehouse_ID
> >
>
> Thx Alan,
>
> I did design it that way, but if feels reassuring to have somebody elses
> opinion. Thanks for your help.
>
> Steven Tolleneer
Received on Tue Jun 27 2000 - 00:00:00 CEST