How do people usually solve this?
Date: 6 Jan 1994 04:28:06 GMT
Message-ID: <2gg40m$i7e_at_canopus.cc.umanitoba.ca>
I'm comparing several solutions of a common problem. Could netters kindly
contribute some comments, or let us know how your database deals with this
problem?
We need to store a person's name, date of birth, etc. A person may have
multiple names (e.g., current name, maiden name, preferred name.)
Some possible designs are:
ADVANTAGE:
)
Very simple SQL to access the information. Fast DB operations.
Guaranteed that every person will have a name because 'name' is
declared (implicitly) NOT NULL.
DISADVANTAGE:
birth_date has to be duplicated for each name_type. Such a design
is not of a high normal form.
2. create table person_table
( person_id int, /* primary key */
birth_date datetime
)
create table person_name
( /* compound primary key: person_id + name_type */
person_id int, /* foreign key */
name_type char(10),
name varchar(30)
)
ADVANTAGE:
A normalized design. Great for classroom teaching.
DISADVANTAGE:
Not guaranteed that every person in person_table has a name in
person_name table. We don't want that to happen.
3. create table person_table
( person_id int, /* primary key */
current_name varchar(30), /* The most important name */
birth_date datetime
)
create table person_name
( /* compound primary key: person_id + name_type */
person_id int,
name_type char(10), /* exclude 'current' */
name varchar(30)
)
ADVANTAGE:
Guaranteed that every person has exactly one current name, and
optionally other names stored in person_name table.
DISADVANTAGE:
Search by name is tricky. If I want to search for 'James', then
in all likelyhood I don't care if "James" is the person's current
name or preferred name. But unfortunately, current name is stored
in person_table and preferred name is in person_name table.
-- Vincent Q. Yin umyin_at_ccu.umanitoba.caReceived on Thu Jan 06 1994 - 05:28:06 CET