Re: How do people usually solve this?

In <2gi818$jeh_at_access3.digex.net> mstein_at_access3.digex.net (Michael P. Stein) writes:

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

This would be great except that I want a little bit more. Since it's very often that a person's name and birth_date are both needed by the user, the view should contain name *and* birth_date, i.e,

	create view person_view as
		select person_id, name_type, name, birth_date
		from person_table
		UNION
		select ????
		from person_name

        123456, 'preferred', 'James', 'Sep 12, 1956' not simply

        123456, 'preferred', 'James', NULL

If such an ideal UNION is not possible, I guess we can live with your suggestion:

	create view person_view as
		select person_id, 'current', name from person_table
		UNION
		select person_id, name_type, name from person_name

and do a separate lookup for birth_date. Thank you for your suggestion.

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

Seems to be a good idea. I'm using Sybase and we have triggers. I feel that solution #1 might be better. Solution #1 prevents a careless programmer from screwing up the database.

-- 

Vincent Q. Yin
umyin_at_ccu.umanitoba.ca