Arved Sandstrom wrote:
> "Stefan Ram" <ram@[EMAIL PROTECTED]
> wrote in message
> news:join-20080505155910@[EMAIL PROTECTED]
>> "Arved Sandstrom" <asandstrom@[EMAIL PROTECTED]
> writes:
>>> named relation****ps [...] so the join tables will
>>> often have at least one extra attribute.
>> Which is the same as keeping a single join table
>> per named relation****p without an extra attribute.
>
> This is true. But you'd want to ask whether or not you want one join
table
> for for work phone numbers, one for personal phone numbers, and possibly
> others for other phone numbers. Easier to have one join table for
> PersonInfo - PhoneNum, and keep an attribute in there that describes the
> nature of the phone number.
In dimensional databases, a fact table is pretty much just what you
desribe -
a many-to-many join table that has foreign key references to multiple
dimension tables, along with facts that pertain to that combination of
intersecting keys.
Returning to the phone number table -- well, phone isn't so good as an
independent entity IMHO, so let's use address instead. An address entity
stands alone pretty well, and it has a network of relation****ps to person
entities, as Arved has described already. That join table, what I call a
"many-to-many" table, might want other attributes that pertain to the
relation****p key. Time of day for primary association, e.g., evenings and
weekends for a home address. Descriptive information, like, "Office
building
with guard at desk". Ranking, e.g., primary, alternate, third choice -
orthogonal to the name. Once you have a table, with rows identified by a
primary key, it's legitimate and frequently useful to attach dependent
columns. There's no rule that says relation****p tables can't have
attributive
columns; /au contraire/ it's a very useful and oft-used idiom.
In any join table (or whatever you want to call them) diligently avoid use
of
an autogenerated surrogate key (i.e., sequence). The natural key of the
table
already comprises key columns that reference other tables' PKs; adding
another
layer of indirection is harmful to your schema structure. This becomes
especially dire if you snowflake your schema - multilayer joins are a real
PITA when they involve superfluous key columns.
--
Lew
|
