[AccessD] Design question

I would guess that it depends on how important such appellations are to 
the app. What is the cost of getting one wrong?

The upside to using an FK table is the ability to add as many titles as 
you could ever want. The downside, which you identified, is the 
verbosity of the SQL -- which can be obviated in several ways, such as 
writing a function to simplify construction of the name (like Title & " 
" & FirstName & etc.), then using that function in all your queries.

What will it gain you to lose the FK table and just allow the users to 
enter anything they wish? One less join, obviously. But how many times 
can one spell "Mr." incorrectly? Mind you, I once read a data-cleansing 
study that said the users of an app in Framingham managed to spell 
Boston 19 different ways. That boggles the mind! But allegedly it's true.

Don't feel bad for programming 20 years and asking this question. I ask 
it every time I do an app. What is really to be gained by separating the 
given name from the last name? In certain apps it's important. In most 
apps I venture to say that it is unimportant, but most of us do it by 
knee-jerk reaction. If the only issue is sorting, then one can easily 
establish the convention that you enter the name "Fuller, Arthur" and on 
those few occasions when you need the given name alone, call a function 
that extracts the rightmost word from the name. (Yeah yeah, I know that 
won't always work, so let's not bother going there.)

Similar issue with company names whose first word is "The". I almost 
always shift the "The" to the end, as in "Bank of America, The"... for 
sensible sorting.

Arthur

Griffiths, Richard wrote:

>Hi Group
>
> 
>
>Design issue - how to store the title for a person record
>
> 
>
>Option 1
>
> 
>
>tblPerson
>
>-------------
>
>titleID
>
>firstname
>
>lastname
>
> 
>
> 
>
>Option 2
>
> 
>
>tblPerson
>
>-------------
>
>title (contains actual value from tblTitle e.g Mr,Miss, Mrs, Ms.,Dr.,
>Prof.)
>
>firstname
>
>lastname
>
> 
>
>Issues
>
>For option 1 - every time we need person details will require a join on
>the table (makes sql verbose etc) - 
>
>For option 2 - how to handle tblTitle changes - could use ref. integrity
>or even simpler.....only allow user to add new titles and not amend
>(can't see any reason to amend
>
>anyway - amend 'Mr' to what??)
>
> 
>
>Anyone care to comment (can't believe I've 20 years programming exp. and
>am asking this type of question - must be my age (just hit 40 ))
>
> 
>
>Thanks
>
> 
>
>Richard Griffiths
>
> 
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 12/9/2004