[UPHPU] Database relationship problem

[Scott Hill]

On 8/16/06, Jon Jensen <jon at jenseng.com> wrote:
>
>
> 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 ' ')


I have never used group_concat before.  I will have to try that.

Personally I'd go the simpler route and have a GivenNames field in the
> Person table with a full-text index on it.


I have done this in the past but not with a full-text index.  I will also
have to try that.  I believe you are right about over-normalization.  I
sometimes have to admit that the given names thing was just a personal
exercise in normalization for me but it always bothered me that the query
was not easy.

Another problem:

Lets say you want to create an address book database of all living
relatives.  I want to order them by generation like a pedigree since I am
always trying to remeber someone from a family reunion by who their parents
are.  You could put a father and mother link in the person table but what
about spouses.  My uncle passed away about 10 years ago and my aunt
remarried.  She now has grown children from both marriages with addresses,
urls, etc. that I want to keep in the database.  How would you design the
table(s) to keep the relationship intact?

-- 
Scott Hill

"May you solve interesting problems" - Author Unknown
"A fanatic is one who can't change his mind and won't change the subject." -
Sir Winston Churchill