Re: Normalization w/Individuals & Entities
Date: 2000/04/27
Message-ID: <8eajva$fjt$2_at_brokaw.wa.com>
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 havea address.
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 suchas first name, last name, title, etc.
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