Re: Normalization w/Individuals & Entities

Consider the following variation:

Webster's defines Agent as: A person or thing that performs an action or brings about a certain result, or that is able to do so. An active force or substance producing an effect. A person, firm, etc. empowered to act for another. Representative of a government agency.

Synonym. An agent is, generally, a person or thing that acts or is capable of acting, or, in this comparison, one who or that which acts, or is empowered to act,, for another.

broker, promoter, operator, representative, salesman, assistant, emissary, appointee, servant, regent, intermediary, abettor, executor, attorney, lawyer, go-between, surrogate, labor leader, procurator, mediary, deputy, principal, factor, minister, envoy, canvasser, middleman, commissioner, syndic, proxy, substitute, factotum, steward, functionary, solicitor, negociant, ambassador, comprador, proctor, negotiator, advocate, coagent, press agent, claim agent, employment agent, actor's agent.

So your individual or entities such as businesses, government entities, and organizations (Acme, City of Anytown, Association of Associates) are all the same thing. Now they can be associated with each other through a many to many relationship.

This lets us create the following structures: Agent Table

   AgentSaid PK Note: A Said is short for System Assigned Identifier

   AgentCompositeName Due to performance the Noman table is used for maintenance

                                        of name and the composite of the
name is held here for reports and forms.
   AgentType                     Indicates if this agent is an individual,
city envoy etc.
   .                                 Other stuff

Location Table
   LocationSaid                 PK
   AddressSaid                  FK to Address. Note a location may not have

   LocationDescription

   .                                 Other stuff

Phone Table
   Phone Said                    PK
   LocationSaid                 FK to Location indicating where the phone #
is to be found.
   .                                 Other stuff

AgentAssociation Table    Doing the queries is always fun on this table
   AgentSaid                     FK to Agent
   AssociationType            Indicates purpose of association such as boss,
company worked for, department , etc.
   AgentSaid                     FK to Agent

AgentLocations Table
   AgentSaid                     FK to Agent
   LocationType                Indicates purpose of location such as home,
work, traval site etc.
   LocationSaid                 FK to Location

AgentPhones Table
   AgentSaid                      FK to Agent
   PhoneType                     Indicates purpose of phone such as home,
work, emergency site etc.
   PhoneSaid                      FK to Phone

Address Table
   AddressSaid                   PK
   .                                  Other stuff such as Zip, Zone etc.

Nomen Table                   Table used to hold the segments of an agents
name. Great for searches
   NomenSaid                    PK
   AgentSaid                      FK to Agent
   SegmentType                 Indicates what the segment this is for such

   SegmentSequence
   SegmentText

This might give you some ideals. These generic tables and others make up a core set for most of the applications I work on.

TomB

Tom Haughton <haughton_at_micron.net> wrote in message news:39062102.AE2A6781_at_micron.net...
> 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 Thu Apr 27 2000 - 00:00:00 CEST