[UPHPU] Database relationship problem

[Jon Jensen]

> My question is what would the SQL look like to retrieve the 
> person with all of their names?

In recent versions of MySQL, you can do something like this to retrieve the
data in one row:

SELECT
 Person.ID,
 GROUP_CONCAT(GivenName.Name SEPARATOR ' ') AS GivenNames,
 Person.Surname
FROM
 Person, Person_GivenName, GivenName
WHERE
 Person.ID = 100
 AND Person.ID = Person_GivenName.`Person ID`
 AND Person_GivenName.`Given ID` = GivenName.ID
GROUP BY
 Person.ID

This would lump all given names for that person into the GivenNames field
delimited by a space (you can use any delimiter you want). Of course, they
might not be in the correct order, so it may be useful to have a list-order
column in the Person_GivenName table. Then you'd say:

  GROUP_CONCAT(givenName.Name ORDER BY ListOrder SEPARATOR ' ') 


> Or is there a better way to represent this relationship?

I believe so. What you've outlined is a fairly reasonable way to do it -- it
is normalized, it makes it easy to do lookups on any given name (assuming
they are indexed), and it most likely uses less space than other approaches.
A major downside is that it complicates searches for "Jim Bob" and other
combinations of given names.

There is such a thing as over-normalization, and I think this is one such
case. To illustrate what I mean, imagine you have a database of articles and
you took this approach for the content of the article (e.g. a table of
articles, a table of words, and a table mapping words to the article). No
rational person would do that. A less extreme example would be if you did
that for street addresses (e.g. "123" "Main" "St."). That wouldn't sense
either, and if you think about it, it's not too different from the
GivenNames example.

Personally I'd go the simpler route and have a GivenNames field in the
Person table with a full-text index on it. It'd make queries extremely
simple and efficient, and depending on the ratio of unique names to persons
in your dataset, it might actually use less disk space than the other
approach (since you'd have two fewer tables). Plus it would automatically
make for an easier UI in the app ... one text field instead of many.

Jon