Re: HOWTO: company & client addresses 1 or 2 tables?

From: Niels van der Kam <n.a.van.der.kam_at_home.nl>
Date: Tue, 10 Jun 2003 09:07:39 +0200
Message-ID: <bc4048$afj$1@news3.tilbu1.nb.home.nl>

Thanks for your reply, but the situation is a bit more difficult. Let me try to explain.

the main table is the my relation table. The company i'm developping for has many relations (some are customers, some are suppliers, some prospects). This relation can be "stand alone", meaning he/she is not connected to a company, but can also be part of a certain company. A company can have more than one relation connected to it.

So my relation table has a foreign Company_ID key.

Now here is my problem. Both companies and "stand alone" customers can have one or MORE addresses. Companies can for instance have a visitor address, a postal address and an address to send bills to. My question thus is this: should I make a table called Address or something that can contain address information and give this table two foreign keys: Company_ID and Relation_ID of which one gets filled? Or should I make two tables one name Company_Address and one named Relation_Address?

Or should I go for an altogether different approach?

Hope you can give me some perspective on this.

Great website by the way. This kind of thing can really come in handy!

Niels

"Barry" <barryw_at_databaseanswers.com> schreef in bericht

news:55816d8a.0306091430.3d6712e1_at_posting.google.com...

> "Niels van der Kam" <n.a.van.der.kam_at_home.nl> wrote in message
news:<bc2j6t$ttg$1_at_news2.tilbu1.nb.home.nl>...
> > Or should I make two tables one for company addresses and one for client
> > addresses?
> > Are there rules for this kind of situations?
> I guess the rule would say 'Identify the Things that occur naturally
> and model them accordingly'.
> In this case, the Things are Addresses and so you should have just one
> Address Entity with a foreign address_id key in the company and client
> tables.
> Here's an example on my Database Answers web site of a Data Model for
> Traffic Cops showing Violaters who have Addresses, (just like Cops).
>
> Barry Williams
> Principal Consultant
> Database Answers
Received on Tue Jun 10 2003 - 02:07:39 CDT