Normalization w/Individuals & Entities

I am working with what should be a common database scenario. Interchangeably I need to work with individuals (Joe Blow, John Doe) and

entities such as businesses, government entities, and organizations
(Acme, City of Anytown, Association of Associates). Often but not
always I will need to work with specific representatives at these different entities (Jane Deer at Acme, Mike Smith at the City of Anytown, Steve Nelson at the Association of Associates). In different cases I may have to work with different representatives or a set of representatives at the same entity (Jane Deer at Acme, Robert Johnson at

Acme, Linda Jensen at Acme, Robert Johnson and Linda Jensen at Acme). These different entities might have multiple locations (Acme at Denver, CO, Acme at Chicago, IL).

The tables that I have worked up to solve this scenario are as follows:

[Address]
This table would store addresses along with an auto increment key field
(AddressID).

[Individuals]
This table stores the names of individuals along with individuals that would represent entities. Each record would also have an auto increment

key field (IndividualID). If the individual is not associated with an entity then an AddressID field could be used.
(Joe Blow, John Doe, Jane Deer, Mike Smith, Steve Nelson, Robert
Johnson, Linda Jensen)

[Entities]
This table stores the names of entities with an auto increment key field

(EntityID).
(Acme, City of Anytown, Association of Associates)

[Entity _at_ Location]
The table would associate Entities (EntityID) with addresses
(AddressID).
(Acme at Denver, CO, Acme at Chicago, IL, City of Anytown at Anytown,
OH, Association of Associates at Boston, MA)

[Delegate]
This table is where data stored as an Individuals and an Entities could be used interchangeably. An auto increment field would be key
(DelegateID). This table would include fields for IndividualID and
EntityID. If only the IndividualID field is used then it is assumed that only the individual themselves is referenced (Joe Blow, John Doe). If both fields are referenced then it is assumed that the IndividualID represents the EntityID (Jane Deer at Acme, Mike Smith at City of Anytown, Steve Nelson at the Association of Associates, Robert Johnson at Acme, Linda Jensen at Acme). If only data is given in the EntitiyID field it assumed that only the entity itself needs to be referenced.

A shortcoming to these tables is that a set of individuals representing an entity could not be used for a specific case. I could make a table for delegate sets however but that does not seem to be efficient. Another issue at hand is should I make a distinction for domestic addresses and international addresses?

For phone numbers should I make a table for all phone numbers. A single record could be for work, home, mobile, pager, fax, and e-mail and in each of these tables associate the numbers to the DelegateID and a field to indicate type of number or e-mail?

I am sure that the issues that I am tackling have been tackled many times before and ideally I would like to find an informative url on the subject matter.

Thanks in advance.

Tom Haughton Received on Tue Apr 25 2000 - 00:00:00 CEST