Re: Designing a structure

From	"David Segall" <david.segall@THRWHITE.remove-dii-this>
Subject	Re: Designing a structure
Message-ID	<j0n024lu78vvaoo46ac92n8lb5h623gkiv@4ax.com> (permalink)
Newsgroups	comp.lang.java.databases
References	<vcETj.521$Yp.53@edtnps92>
Date	2011-04-27 15:21 +0000
Organization	TDS.net

Show all headers | View raw

  To: comp.databases,comp.lang.
"Arved Sandstrom" <asandstrom@accesswave.ca> wrote:

>"Stefan Ram" <ram@zedat.fu-berlin.de> wrote in message 
>news:database-20080503180716@ram.dialup.fu-berlin.de...
>> David Segall <david@address.invalid> writes:
>>>For most purposes I want to communicate with a family or
>>>business at its "home" address. For example, "Fred and Betty
>>>Bloggs" or "Acme Widgets" have a specified street address and
>>>telephone number. It is possible that the Bloggs family has a
>>>holiday house or I have to deal with Acme Widgets at more than
>>>one location.
>>
>>  If a family can have n houses, that is a 1:n-relation, and its
>>  implementation is being described in every RDBMS textbook.
>>
>>>I also need information about individuals such as mobile phone
>>>numbers and birthdays but I don't want to duplicate shared home
>>>and business addresses and telephone numbers. For example,
>>>Betty Bloggs could work for Acme Widgets and share a town house
>>>and a country house with Fred.
>>
>>  Then have one table for persons, one for houses and one for
>>  the n:m-relation (standard textbook material).
>>
>>>have been frustrated by the address books in many applications
>>>that insist you supply all the details for every individual.
>>
>>  If you do not want a field to be mandatory, you are free
>>  to design so.
>>
>>  Just design which entities and relations you want to
>>  model and which attributes are mandatory and which not.
>>  Then implement this and you are done.
>
>I tend to agree. The basic entities are fairly clearcut: addresses, personal 
>(individual/business) info, phone numbers, email addresses. There are simply 
>going to be lots of relationships, and in addition they are going to be 
>named relationships (so one can designate a work phone number as opposed to 
>a personal phone number, or a primary home address as opposed to a cottage), 
>so the join tables will often have at least one extra attribute.
>
>As far as people sharing addresses, say, like a couple. Well, that's not 
>really a database problem, per se. The address itself only needs to be 
>stored once - there will simply be two PersonInfo-Address relationships. To 
>avoid having to enter the data twice is really more a function of the 
>application that one designs to enter (and display) the data. In other 
>words, I'd write the application so that it is quite flexible at 
>searching/retrieving entities and allowing new links (relationships) to be 
>established.
>
>Myself I would keep personal relationships (e.g. couple, family, roommates 
>etc) entirely separate. That is, if you want to say that Person A is married 
>to Person B, use a separate join table for that. Because personal 
>relationships do not invariably imply any other linkages.
>
>To recap, I see most of the work being asociated with the interface, not the 
>actual database.
Thanks Arved. I agree that the application probably reduces to a
many-to-many relationship between persons and businesses in one table
and locations in another and that does lead to the difficult and
probably off-topic question of designing a GUI to represent that
relationship. In my sheltered world of designing databases for small
businesses I have been able to insist that the rows in each table are
present before the user specifies a many-to-many join. I don't think
that is practical for an address book. 

It is true that the many-to-many "personal relationships" are ideally
represented by a separate junction table but I don't think that I can
expect a user to enter those relationships and I am prepared to accept
that my database will not be able to associate two people who do not
share a location.

Thanks to the posts in this thread I now realize my original question
was wrong. What I am really looking for is a "true and correct :)"
data model for the _user_ of this many-to-many relationship. Failing
that, I have some ideas for a GUI but they are really clunky and
involve Outlook/Thunderbird-style tabbed panes with an added drop-down
list to enable the user to select existing data.

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