"Arved Sandstrom" <asandstrom@[EMAIL PROTECTED]
> wrote:
>"Stefan Ram" <ram@[EMAIL PROTECTED]
> wrote in message
>news:database-20080503180716@[EMAIL PROTECTED]
>> David Segall <david@[EMAIL PROTECTED]
> 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 relation****ps, and in addition they are going to be
>named relation****ps (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 relation****ps.
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 (relation****ps) to
be
>established.
>
>Myself I would keep personal relation****ps (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
>relation****ps 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 relation****p 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
relation****p. 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 relation****ps" are ideally
represented by a separate junction table but I don't think that I can
expect a user to enter those relation****ps 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 relation****p. 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.
|
