Re: How do people usually solve this?

>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
        

Could be expensive.

I would add a level of abstraction and make the select a stored procedure; this does several things, including allowing you to change the underlying implementation.

That being the case, I would ask myself to gauge the probabilities of data being in one table or the other. If, for instance, the person_name table is small, then the cost of the union would be minor. If it is large, but there is a large probability that the name the user is looking for is the current one, then the union would be more expensive than it is worth (instead, select from pt and if nothing comes back then select from pn). If the probability is near uniform, then use the second table c onfiguration (no name in pt).

If the average number of names per person is near 1, then you could probably go with a join. If it is more, you may be better off splitting it into two querries (depending on such things as indexes and what else is in pt).

My best advice (the advice I give myself) is to construct several versions and several querry strategies and look at the i/o performance for different cases. Then, by weighting the probability of each case, choose the least expensive.

Topher

---
Christopher A. White          | "I once could see, 
McCaw Cellular Communications |      but now, at last, I'm blind."
Kirkland, Wa                  |         -- Dream Theatre