Table/Relationship - trying to follow best practices. Suggestions?

[Devin]

Hello.

I'm setting up a small database
of user account and some other information pertaining to them (less
than 30). Its probably insiginificant how I set this up for the most
part but I would like to follow best practices as a matter of
experience.


I have a table of users. Fname, Lname, email_address. The problem is
that its possible that a user might have more than one email address.
So I can put them in one field and use a split function when I
actually call that data, which seems a lot like cheating. Or I can
make another table. If I make another table I'm not sure what fields
it should have. The only way I can think to uniquely identify each
record would be a composite key, but I understand that isn't really
proper unless both parts of the key are from existing keys. (In this
case I'd be using the userID and an auto-number)


Suggestions?


Sorry if this isn't appropriate for this group, I couldn't find
anything more specific to just "database help".



Thanks!

 

[Douglas J. Steele]

The two-table approach would definitely be better.

The second table would require whatever field(s) uniquely identify a record
in the first table plus a text field to hold the e-mail address. You might
also want another field to indicate what type of e-mail address it is (home,
work, preferred home, etc.)

A good group to ask questions such as this would be
microsoft.public.access.tablesdbdesign

 

[John Spencer]

Your design instincts are good.

If the UserID is unique for each user then your email table would have store
the user id as the foreign key

Table - Email Addresses
UserID - one of the UserID values in the User table.
EmailAddress - the Email Address

You could make the two fields a composite primary key. This would prevent you
from entering the same email address twice for any particular user.

Alternative is to add a primary key based on an autonumber. If you did that I
would consider making a composite unique index on the UserID and EmailAddress
field to prevent accidental duplication. That may or may not be important
depending on the functionality of your database.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Devin

I'll add another twist to the excellent advice Doug and John have posted...

It is somewhat unlikely that the same email address would be 'shared' by
multiple individuals.

However, it is not at all uncommon for the same address or same phone number
to be 'shared'.

If you want to pursue the same approach when dealing with addresses and/or
phone numbers, you will need THREE tables...

One table for persons.
One table for addresses (or phone numbers).
One table for person-connected-to-address.

This design allows one person to have multiple addresses (or phone numbers),
and one address (...) to be used by multiple persons.

As I mentioned, probably unlikely for email addresses, but critical if you
are working with families or with people working at organizations!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

To throw in my six pennyworth:

As regards the second (users' emails) table's primary key I'd suggest it be
a composite one, UserID and Email, as these two values in combination can
only legitimately exist once in the table (this makes them a 'candidate
key'). There is no need to include an autonumber column as the key of the
users' emails table therefore, and it doesn’t serve any real purpose to have
one, but if you do, then its imperative, as John advised, that you also
create a unique index on the userID and email columns (in combination, not
individually).

The above applies whether you have one person per email address, or as Jeff
has explained, a design which allows multiple people per email address, in
which case the email column is, like the userID column, also foreign key, in
this case referring the key of the emails table. The users' emails table
would in that case be modelling a many-to-many relationship between users and
emails (more likely with phone numbers of course, as Jeff said).

Note that with Jeff's scenario the emails table does not need a separate
emailID primary key such as an autonumber as the addresses are unique. So the
address itself can be the 'natural' key. Users do need a 'surrogate' userID
key however as names can be duplicated even in small workgroups. As I
frequently bore people here by saying, I once worked with two Maggie Taylors.
Even complex keys, as are sometimes unadvisedly used, made up of names, date
of birth, gender etc are not safe. I recall one clinic at which I was
present where two patients with the same names, both female, and the same
date of birth turned up on the same day!

When 'natural' keys are used its essential that cascade updates are enforced
when the relationship is created. Again this is more likely to be the case
with phone numbers, e.g. when some years ago all the UK area codes were
extended to create greater capacity in the system it was necessary to amend
all the phone numbers in databases. With cascade updates enforced changes
made in the phone numbers table are automatically made in the users' phone
numbers table, so all that was necessary was to execute an update query on
the one table.

The enforcement of cascade deletes requires more careful consideration,
however. The automatic deletion of the related contacts' email, phone number
etc rows in related tables makes sense if the contact is deleted from a
Contacts table. On the other hand if you delete a city from a Cities table
you would probably not want to automatically delete all contacts in that
city. More likely you'd just want to enforce referential integrity so that
you could not delete a city if there were one or more contacts currently in
the database for that city.

Ken Sheridan
Stafford, England