Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #68

Re: Designing a structure

Show all headers | View raw

  To: comp.databases,comp.lang.
Arved Sandstrom wrote:
> "Stefan Ram" <ram@zedat.fu-berlin.de> wrote in message 
> news:join-20080505155910@ram.dialup.fu-berlin.de...
>> "Arved Sandstrom" <asandstrom@accesswave.ca> writes:
>>> named relationships [...] so the join tables will
>>> often have at least one extra attribute.
>>  Which is the same as keeping a single join table
>>  per named relationship 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 relationships 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 
relationship 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 relationship 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

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

Back to comp.lang.java.databases | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Designing a structure for "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
  Re: Designing a structure "Ed Prochak" <ed.prochak@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: Designing a structure "Martin Gregorie" <martin.gregorie@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Ed Prochak" <ed.prochak@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: Designing a structure "Ed Prochak" <ed.prochak@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
            Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
              Re: Designing a structure "David Cressey" <david.cressey@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
                Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
              Re: Designing a structure "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
                Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
                Re: Designing a structure "Ed Prochak" <ed.prochak@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
                Re: Designing a structure "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
                Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
            Re: Designing a structure "Gene Wirchenko" <gene.wirchenko@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Roedy Green" <roedy.green@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
  Re: Designing a structure "ram" <ram@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
    Re: Designing a structure "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: Designing a structure "ram" <ram@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: Designing a structure "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Lew" <lew@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
      Re: Designing a structure "David Segall" <david.segall@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
        Re: Designing a structure "Marco" <marco@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
          Re: Designing a structure "Arved Sandstrom" <arved.sandstrom@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000
  Re: Designing a structure "Marco" <marco@THRWHITE.remove-dii-this> - 2011-04-27 15:21 +0000

csiph-web