Hello, I thinking about adding an address book to an already-existing set of applications and am just wondering about the best database design for it. I don't do this sort of thing often and I'd like to hear others' opinions.
Requirements:
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)".
Depending on the purpose the individual address entry will be put
to, it may also require further details like:
- email address
- postal address
- company name
- landline number
- mobile phone number
- Jabber id
- Twitter username
- Blog URI
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.
The list of valid purposes ("billing, "technical", ...) and valid
details ("email", "landline", "mobile", "twitter", ...) need not be
editable by the user. They can be limited to what the applications
support.
Each user should be able to have multiple address entries of the
same purpose, so for example it would be valid to have two entries
marked as "billing" (they'd both get copies of invoices).
I'm thinking something like this:
.----------------------------------------------------------------.
|addr_book |
+----------------------------------------------------------------+
|id Primary key |
|user_id Foreign key to the users table |
`----------------------------------------------------------------'
.----------------------------------------------------------------.
|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)" |
`----------------------------------------------------------------'
.----------------------------------------------------------------.
|abe_detail |
+----------------------------------------------------------------+
|id Primary key |
|abe_id Foreign key to the ab_entry table |
|type Type of detail, e.g. "landline", "mobile", "jabber" |
|data The actual contact data, e.g. "0123 456 7890", |
| "b...@example.com", ... |
`----------------------------------------------------------------'
.----------------------------------------------------------------.
|addr_purpose |
+----------------------------------------------------------------+
|id Primary key |
|abe_id Foreign key to ab_entry table |
|purpose Purpose of this entry, e.g. "billing", "technical", |
| ... |
`----------------------------------------------------------------'
So, to get every address book entry for a given user:
SELECT ab_entry.id, ab_entry.name, ab_entry.nickname
FROM addr_book, ab_entry
WHERE addr_book.id=ab_entry.ab_id
AND addr_book.user_id = $some_user
To get the email address of every billing contact for a user:
SELECT ab_entry.name, abe_detail.data
FROM addr_book, ab_entry, abe_detail, addr_purpose
WHERE addr_book.id=ab_entry.ab_id
AND ab_entry.id=abe_detail.abe_id
AND ab_entry.id=addr_purpose.abe_id
AND addr_purpose.purpose="billing"
AND abe_detail.type="email"
AND addr_book.user_id=$some_user
What do you think?
In some ways this seems excessive - I mean, there's a four way join
there, just to get a list of email addresses.
In other ways it seems like not quite enough; I've skated over
abe_detail.type and addr_purpose.purpose being textual in nature.
They're going to have to be short strings used by the applications
and the longer human-readable version ("A landline phone number",
"Postal/ZIP code", "Technical contact", ...) is going to have to
come from somewhere.
There is a temptation for extra tables for these:
.----------------------------------------------------------------.
|addr_purpose |
+----------------------------------------------------------------+
|id Primary key |
|abe_id Foreign key to ab_entry table |
|apd_id Foreign key to the addr_purpose_descr table |
`----------------------------------------------------------------'
.----------------------------------------------------------------.
|addr_purpose_descr |
+----------------------------------------------------------------+
|id Primary key |
|descr Purpose of this entry, e.g. "Billing matters", |
| "Technical contact", ... |
`----------------------------------------------------------------'
and so on..
Cheers,
Andy
signature.asc
Description: Digital signature
-- Please post to: Hampshire@mailman.lug.org.uk Web Interface: https://mailman.lug.org.uk/mailman/listinfo/hampshire LUG URL: http://www.hantslug.org.uk --------------------------------------------------------------
