Re: How do people usually solve this?

In article <2gg40m$i7e_at_canopus.cc.umanitoba.ca>, umyin_at_cc.umanitoba.ca (Qing Yin) writes:
|> 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:
|>
|>
|> 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.
|>

How about explicitly declaring name NOT NULL. Using the example above this would yeild:

    create table person_name

 	(	/* compound primary key: person_id + name_type */
 		person_id	int	NOT NULL,	/* foreign key */
 		name_type	char(10)	NOT NULL,
 		name	varchar(30)	NOT NULL
 	)

This would eliminate the disadvantage since Sybase would require an entry for name (in addition to person_id and name-type)

-- 
Lonnie Barnett
Mead Data Central                                     (513) 865-1038
P. O. Box 933                                    lonnie_at_meaddata.com
Dayton, Ohio 45401                         ...!uunet!meaddata!lonnie