Re: How do people usually solve this?

umyin_at_cc.umanitoba.ca (Qing Yin) writes:

>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
>Now if the user search by name for 'James', and 'James' happens to be a
>preferred name but not current name, the birth_date should still be
>displayed. That is, I wish the return value to be:
> 123456, 'preferred', 'James', 'Sep 12, 1956'
>not simply
> 123456, 'preferred', 'James', NULL

how 'bout:

	create view person_view as
  	  select person_id, 'current', name, birth_date
	  from person_table
	  UNION
	  select pn.person_id, pn.name_type, pn.name, pt.birth_date 
	  from person_name pn, person_table pt 
	  where pn.person_id = pt.person_id 
	

Mike

-- 
 C ^ ^ D _  mdchachi_at_vela.oakland.edu  // :  elgn /  mata  nihon  e  //
  ^0 0^  __                          //   : air /  ikitai desu yo  //
  ( v )      Woof.                 // .ri :t. /                  //
    U    (or is it Moo?)         //  ght  : /  BRAIN MODERNITY //