Re: [Hampshire] Database design for an address book

Hi Andy,

On 18/12/2011 21:06, Andy Smith wrote:
>
> Assume there's an already-existing table of users.
>
> Each user should be able to define multiple addresses. Let's call
> their collection of addresses their address book.
>
> An address will at its simplest be a name and an entry name, so the
> user can distinguish different addresses from each other without
> having to read all the details, e.g. "Me (Work)".

How is this going to be used and by whom? Is it just a PIM tool for the
user or will others be able to query it -- you mention 'emergency
contact' below, which implies query by others? What about scale: how
many users do you expect?

> The point is, it won't be possible to enumerate all possible address
> details that could ever be useful, so they can't be columns of a
> database table.
>
> The different purposes might be things like:
>
> - billing
> - emergency contact
> - technical contact
>
> Each purpose would have different address details that were
> required. For example, "billing" might require at least an email
> address and postal address while "emergency contact" might require
> at least a mobile phone number and email address. The applications
> will have to enforce that.

Required by whom? Please don't confuse what *you* consider required
information with what the *user* considers to be required. If you do,
you'll end up with subverted entries, e.g. a normal entry with a
nickname 'Emergency contact' and no phone number or, worse, an emergency
contact with a bogus phone number.

I am consistently irritated by online shops that insist on a phone
number 'in case something goes wrong with your order'. I don't want
them to phone me, ever. Not under any circumstances. They have my
email address (begrudgingly, but that's another rant) and that is
sufficient.

> .----------------------------------------------------------------.
> |addr_book                                                       |
> +----------------------------------------------------------------+
> |id          Primary key                                         |
> |user_id     Foreign key to the users table                      |
> `----------------------------------------------------------------'

How many address books do you expect most people to need? Rather than a
hierarchical design, perhaps it would be better to have a tag/keyword
based design. There are quite a few entries in my address book that
don't fit neatly into one category (a friend and a colleague, for
example) and I find that a hierarchical design imposes artificial
boundaries in this case.

> .----------------------------------------------------------------.
> |ab_entry                                                        |
> +----------------------------------------------------------------+
> |id          Primary key                                         |
> |ab_id       Foreign key to addr_book table                      |
> |name        Name of the person this entry relates to            |
> |nickname    Nickname for the entry, e.g. "Me (Work)"            |
> `----------------------------------------------------------------'

[...]
> In some ways this seems excessive - I mean, there's a four way join
> there, just to get a list of email addresses.

Again, what is the scale of this? How many users, queries, updates?
Joins aren't that big a deal if your indexes are well chosen, but
perhaps it might be better to put the 'usual' data as columns in
ab_entry, leaving abe_detail for additional data. That way you get an
efficient summary lookup.

Chris
--
Chris Smith <cjs94@???>