How do people usually solve this?

From: Qing Yin <umyin_at_cc.umanitoba.ca>
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:

  1. create table person_table ( /* compound key: person_id + name_type */ person_id int, name_type char(10), /* 'current', 'maiden', etc. */ name varchar(30), birth_date datetime
    )

   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.ca
Received on Thu Jan 06 1994 - 05:28:06 CET

Original text of this message