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

Groups > comp.lang.java.databases > #36 > unrolled thread

Designing a structure for

Back to article view | Back to comp.lang.java.databases

Contents

  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

Page 2 of 2 — ← Prev page 1 [2]

#59 — Re: Designing a structure

  To: comp.databases,comp.lang.
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.

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

[toc] | [prev] | [next] | [standalone]

#63 — Re: Designing a structure

  To: comp.databases,comp.lang.
"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.

AHS

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

[toc] | [prev] | [next] | [standalone]

#66 — Re: Designing a structure

  To: comp.databases,comp.lang.
"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.

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

[toc] | [prev] | [next] | [standalone]

#67 — Re: Designing a structure

  To: comp.databases,comp.lang.
"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.

AHS

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

[toc] | [prev] | [next] | [standalone]

#68 — Re: Designing a structure

  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

[toc] | [prev] | [next] | [standalone]

#70 — Re: Designing a structure

  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

[toc] | [prev] | [next] | [standalone]

#72 — Re: Designing a structure

  To: comp.databases,comp.lang.
On May 6, 4:22 pm, David Segall <da...@address.invalid> wrote:

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

Not impossible to implement in a user-friendly way: on the edit form
of one person the user sees the list of addresses and the "+" button,
which leads to the address entry form. After the user clicks "OK" on
the latter, the application checks whether the address already exists
or not (even by applying some euristics, e.g.: "High St." is supposed
== to "High Street"), and proposes to select among possibly equivalent
addresses. New address is only added (and a record on the bridge table
with it) if no existing one exists or none is selected by the user.


> 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's not practical in your case, since the bridge table conceptually
doesn't belong in the high level object model, it is something needed
at the relational level, to overcome the limitations of the relational
model. So it's not wise to show it to the user.

> 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

See above. The junction (or bridge) table is indeed the only way to
truly represent a n-m relation on the SQL side. Or at least the
canonical and most efficient one.

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

You could have Person->Address as 1-1 or 1-n relation, and you could
later compute relations like "live together" by analysing the
addresses of two people. And propose to the user to confirm they live
together. Would be more powerful, for it could be generalized into
"they don't leave together, but are friends", the first step in
evolving your address book application toward a Social Networking
application...

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

I would give a look to to existing models of this type and I would be
at least curious of modelling approaches which are more expressive
than SQL (e.g. ORM, XML or RDF/RDFS/OWL).

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

[toc] | [prev] | [next] | [standalone]

#77 — Re: Designing a structure

  To: comp.databases,comp.lang.
Marco wrote:

> On May 6, 4:22 pm, David Segall <da...@address.invalid> wrote:
> 
>> 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.
> 
> Not impossible to implement in a user-friendly way: on the edit form
> of one person the user sees the list of addresses and the "+" button,
> which leads to the address entry form. After the user clicks "OK" on
> the latter, the application checks whether the address already exists
> or not (even by applying some euristics, e.g.: "High St." is supposed
> == to "High Street"), and proposes to select among possibly equivalent
> addresses. New address is only added (and a record on the bridge table
> with it) if no existing one exists or none is selected by the user.

Potentially some form of autocomplete might be worth considering, at least
for address. Nothing new - I believe GMail does it for addresses. Street
name is likely the most authoritative field; other fields are either not
selective enough (like unit number) or have different possibilities (like
municipality).

Something like what you suggest clearly has to be available for the general
case, or where autocomplete fails due to typos ("how the hell d'you spell
that anyway?").
 
[ SNIP ]
AHS
-- 
* change 'two' to '2' to email me

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

[toc] | [prev] | [next] | [standalone]

#69 — Re: Designing a structure

  To: comp.databases,comp.lang.
On Apr 26, 3:33 pm, David Segall <da...@address.invalid> wrote:

> Please ignore any database structure implied in the preceding
> paragraph. I'm looking for the ideal PIM database structure and you
> probably understand the requirements better than I do. No doubt you
> have been frustrated by the address books in many applications that
> insist you supply all the details for every individual.
>

You may be interested in some of the standards available for your
application domain. E.g.: vCard, FOAF, hCard.

The general question you make sounds like: do I need to reinvent the
wheel every time I need a new model for my new application? If there
are libraries and design patterns for coding, aren't there reference
proposals for application domains?

There are indeed, in recent years a lot of work has been done on XML
formats, microformats, ontologies and Semantic Web. So, in many cases
it's likely one has not to start from scratch. Although, in my
experience, almost always you will need customization and adaptation
of what you find out there.

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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.java.databases

csiph-web