On May 6, 4:22 pm, David Segall <da...@[EMAIL PROTECTED]
> wrote:
> 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.
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 relation****ps" 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 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.
>
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 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.
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).
|
