Re: Entity data spread over multiple tables

Hello John,

The "Participants" table is called Person. A Person has many Locations. A Person has many Locations and one Location may have many Persons.

The relation that resolves this many-to-many is called the PersonLocation. Person, Location and PersonLocation are all classes.

tblPerson
Id AutoIncrement, Unique
Name Text
FirstName Text
MiddleInitial Text
LastName Text
etc. ........

tblLocation
Id AutoIncrement, Unique
StreetAddress Text
City Text
State Text
ZipCode Text
etc. .......

PersonLocation
PersonId
LocationId
Floor Text
SuiteNumber Text
PhoneNumber Text
FAXNumber Text
etc. ........

Note: All "pieces of a Participants identity" belong in the Person table. Not all over the place. These are called attributes, which are physical characteristics of a Person. This is one reason address attributes belong in Location. Address attributes are not physical characteristics of a Person. You can't point to yourself and show me "your city".

Regards . . .

"John O'Conner" <jsoconner_at_earthlink.nospam.net> wrote in message news:2GMQb.26802$zj7.13126_at_newsread1.news.pas.earthlink.net...
> Hi all,
>
> In my Little League db, PARTICIPANTS have 0, 1, 2, or 3 different PHONE
> numbers. I've modeled this by creating a separate PHONE table containing
> a number, phone type, and participant id.
>
> Is this the typical design for attaching 0 or more of the same attribute
> to an entity? I see the benefit being that no PHONE columns are used
> when a PARTICIPANT doesn't have one...with the downside that PHONE is a
> separate table, and I have to access a second table to collect all info
> for a single PARTICIPANT. Actually, I have several different tables with
> pieces of a PARTICIPANTS identity. Placing data about a single entity in
> other tables must be a known, common situation with anthing but trivial
> databases, right?
>
> This rubs me a little funny because I would model this differently in a
> programming language like Java. I would probably make PHONE an array
> within PARTICIPANT classes. The array could contain 0 or more actual
> PHONE instances. The PHONE info is right there with the PARTICIPANT
> object in the programming language...however, my db model is quite
> different. Bothersome....do other people hit this type of modeling
> problem often? Is this a common db design pattern?
>
> Regards,
> John O'Conner
Received on Mon Feb 02 2004 - 22:11:55 CET