Normalization and N-ary relations

I am creating a very simple addressbook database, but I have some ideas about normalization that I am not entirely sure about. The questions below are related (no pun intended), but still slightly different angles.

Here's my specific example (most columns removed for clarity): table "people" contains names etc. and has an artificial primary key, p_id. table "url" contains the various URLs (e-mail, web) for people. Artificial PK url_id. table "peopleurl" links the two above tables, has only two columns; p_id and url_id. So far, so good. Now, table "urltype" contains the protocol for the URL ("http://", "mailto:" and so on), identified by an artificial key. create table urltype (urltype_id bigint, urlprotocol varchar(10)) It will also have an artificial key, urltype_id. Should this be referenced by "url" or by "peopleurl"?

One person can have multiple URLs, each URL can have only one type. Each URL type is used by many URLs.

Should I have "urltype" as a separate table at all? Or merge into one of the other tables. I want to keep it separate to have multiple e-mail addresses and web sites per person and still be able to find a specific type (select only business e-mail addresses). Perhaps even another table to describe which e-mail addresses are private and which are at work.

1. Any rule of thumb for when a column of duplicate/repeating values should be pulled out into a separate table together with the PK or a new artificial key? A typically bad example is "first name", where there may be several people named "Joe", but it doesn't really justify using a first name table and say that employee ID 32489237 has first name number 3242... A better example is perhaps where all the repeating values should be easily updateable, for instance "product category".
2. N-ary relationships. Are these OK, or should they be "resolved" somehow? In the example above, the relations could and up looking like

                     urltype_id-- urltype
                     |
                     V

So peopleurl becomes
p_id bigint, u_id bigint, urltype_id bigint

The other option is
people --p_id--> peopleurl <--u_id-- url <--urltype_id-- urltype

I am wondering whether I am overnormalizing since I get a crowd of tables for phone number also, describing various "types" of numbers, some shared by several persons in the people table.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 17 2000 - 20:48:05 CEST