Managing Child Content in Parent Table

[randydop]

I'm beginning a design where I want to provide flexibility when it comes to a person's name. Most names have first, middle, and last but others have other types of names including title, alternate, nickname, and others. I want to store each individual name component in a simple child table with an index UUID field referencing the parent record, name label, and value. I would use a portal to allow the entry of various types of name types. 

I would like to store the results of the name entries in the child table in different formats in the parent record. One with first, middle, last and another with last, first, middle. There may be others as well.

What's the approach I should take to make this work? I'm assuming a script using triggers to do the field creation in the parent record. I've looked around for an example but have not been successful.

Thanks for any suggestions or ideas.

[David Jondreau]

Why do this?

There are two elements to what you're asking. One is storing different elements of a name. In general, I don't store middle names in a field in a table without a good reason. I sometimes don't even have a separate first and last name (rather than a single name field). Why nickname and alternate? Why all these different attributes? How will this help your users?

The second issue is that you're also saying you want to store each name element in a separate record. Why would you do that??

[comment]

I would like to store the results of the name entries in the child table in different formats in the parent record. One with first, middle, last and another with last, first, middle. There may be others as well.

This is not a good idea, because it breaks normalization. The various formats of a name, derived by concatenating certain components of a name in certain order, should be calculated, not stored. Otherwise every change in a name component would have to be made several times. This is exactly what a relational database is designed to prevent.

The other thing is that unless a person is allowed to have several name components of the same type (e.g. two first names and/or three last names), the natural place for these components is in dedicated fields of the parent table. This will also make the calculations easier, since getting the value of a related record by type is not exactly trivial (see here for one possible approach: http://www.briandunning.com/cf/908).