Re: Entity data spread over multiple tables

"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?

It is the typical design for representing associated data with a similar relative cardinality.

> 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.

Neither columns nor tables are expensive resources at the logical level; they are simply names for identifying one's data. As you mention, having another name can help to deal with missing information.

> Actually, I have several different tables with
> pieces of a PARTICIPANTS identity.

Are they really pieces of a participants entity? Or are some of them separate entities of their own?

> Placing data about a single entity in
> other tables must be a known, common situation with anthing but trivial
> databases, right?

One makes many logical statements about a single entity. Of course, one uses multiple sentences. Would you write your autobiography with a single sentence?

> This rubs me a little funny because I would model this differently in a
> programming language like Java.

Learning something new often requires we leave our comfort zone.

> I would probably make PHONE an array
> within PARTICIPANT classes.

Why not a list? Or a collection? Or an associative array? Regardless, arbitrary physical distinctions at the logical level only limit one needlessly.

> The array could contain 0 or more actual
> PHONE instances. The PHONE info is right there with the PARTICIPANT
> object in the programming language

Location is irrelevant to the logical model. If one clusters the PHONE table and the PARTICIPANT table in the same physical store, the PHONE info is still right there with the PARTICIPANT info.

>...however, my db model is quite
> different.

Yes, indeed. A database model is logical and not physical. Why would you expect it to be the same? Relations facilitate making formal logic statements about data, while arrays do not.

> Bothersome....do other people hit this type of modeling
> problem often? Is this a common db design pattern?

Bothersome? Nay, liberating!

Now, to your phones design. Are you sure you want a separate relation? What are these three phone numbers? If one is a voice phone, one a fax and the other a modem, then they are very different things. On the other hand, if each is a voice phone, perhaps they are the same thing.

In Java, how would you describe and manipulate the equivalent of a view?

Everything depends on your business rules, and I suspect you will have more rules than you can comfortably communicate on usenet. Received on Sun Jan 25 2004 - 17:11:13 CET