Re: Entity Overlap and Relationships

GB wrote:
> 1. What is the "industry best practice" in the following situation:
>
> Say there are 3 types of entities {Entity1, Entity2, Entity3}
>
> Each Entity type has unique attributes and similar ones.
>
> For the attributes that are unique to each Entity it is easy (either
> they are in the table with the Entity or in the case of a one-to-many
> relationship there is a table related only to that Entity).
>
> In the case where there are similar attributes (i.e. each Entity has
> multiple addresses - all of the same form { addressline1, addressline2,
> city, state, zip, etc }) what is the proper way to handle this? I was
> thinking of creating the following tables for each entity:
> Entity1Address, Entity1Phone, Entity2Address, Entity2Phone,
> Entity3Address, Entity3Phone with the Address and Phone tables all the
> same but related only to the relevant Entity table but something rubs
> me the wrong way here...

I'll give this first one a shot. If I am understanding your question, you would likely want an Address table with a generated key. Then each of your Entity1-3 tables would have an attribute that is a foreign key to the Address table.

I wouldn't typically do the same thing with phones, even if storing PhoneType PhoneNumber pairs, as there is an ongoing cost for such designs. But if you need to store multiple phones for an Entity (and are saddled with a SQL-DBMS), then that would be OK. You might instead have an attribute for BusinessPhone, HomePhone, and MobilePhone for each Entity. You are out of luck if you have two business phone numbers then, but this is often sufficient and I suppose you could add a catch-all OtherPhone. You will obviously need to know your precise requirements.

I hope that helps. --dawn Received on Wed Apr 19 2006 - 18:51:35 CEST