This sounds like a good candidate for the two-table model: Table 1 contains an ID and the basic names.
Table 2 contains the 1-many relationship of basic name entry mapped to all the variants - one record per variant. So you might have Table 1 containing ID, Prefix, GivenNames, Surname, Suffix and Table 2 containing ID, Type, AlternateName where Type is one of Prefix, GivenNames, Surname, Suffix. example: Table 1: ID Prefix GivenNames Surname Suffix 123 Miss Mary Elizabeth Stotts III Table 2: ID Type AlternateName 123 Prefix Miss 123 Prefix Ms. 123 GivenNames Mary Elizabeth 123 GivenNames Marg Elizabeth 123 GivenNames Mary Ellen 123 GivenNames Marg Ellen 123 Surname Stotts 123 Surname Stitts 123 Surname Stutts Note that you include all alternatives, including the "default" ones, for ease of constructing queries. Queries would be constructed in terms of a join based on ID: select Table2.AlternateName from Table1,Table2 where Table1.ID = Table2.ID and Table2.Type = "GivenNames" and Table1.ID = '123' Hope this helps. - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
