Re: How do people usually solve this?

>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.

    Solutions to disadvantage:

1. Create a view which is a union of the preferred and secondary names. Minimizes storage at expense of speed.
2. If database triggers are available, after insert on person_table, force an insert into person_name with a record matching the preferred name. One duplicate name storage per person, but this is fastest. (Also must remember to deal with update of name in person_table.)

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.