How to handle pseudonyms and names variants in a person relation

[DaCo]

I'm putting together material (tutorials, etc.) in order to establish a relational database for classical CDs. It should necessarily be a many-to-many, when a CD having one or more composers and one or more artists, and these may occur on one or more CDs. All this is trivial, but how do I handle pseudonyms and name variations?

Example: The composer Dmitri Shostakovich's name is / can be written in many ways on the many CD releases.

When I am in my CD database and draws information from the composer database, the data here of course only should be registered in one place and here I choose the English / international spelling by default.

I suspect that the default name and all name variants must have some kind of a ID number, and that is what I refer to?

This is a problem I have not been able to google me to something useful about, but as it is an important feature I would like allready now include it in my planning of databases and relationships. I have only just started and ahead there awaits many hours of reading and searching the basic technique, but I will be happy for a few overall advice for how I structure this.

P.S. One must assume that many have created databases for classical music. Is there anyone here who can refer to Internet sites that describe different solutions or maybe even a FileMaker template (I've only got version 11 Pro Advanced)?

[Wim Decorte]

Properly normalized you would have a "People" table, and a "Names" table.  Each have their own primary key ("ID").  The Names table also has a foreign key for the person: peopleID.  This way you can have multiple names for one person.

 

Then you have your CDs table and a join between the CD and the People: AppearsOn.

For each person that is featured on a CD you have record there and also include their role (conductor, musician,...), and the nameID under which they appear.

[LaRetta]

I have only just started and ahead there awaits many hours of reading and searching the basic technique, but I will be happy for a few overall advice for how I structure this.

 

Is there anyone here who can refer to Internet sites that describe different solutions or maybe even a FileMaker template (I've only got version 11 Pro Advanced)?

 

You've been given something far better than a solution or template ... you've been given the principle to good design ... use meaningless IDs instead of names and design relational structures based upon those keys.

[comment]

If I understand your question correctly, you need to enter all names/alternative spellings/aliases of an artist's name as a return-separated list in a field in the Artists table (or into individual repetitions of a repeating field). Then use this field as the matchfield in a relationship to the table that lists the people participating in a CD (or in a track).

 

Strictly speaking, this relationship should be used only to lookup (or select) the ArtistID of the artist in question. However, unless there's a danger of two or more artists having the same name variant, I don't see why you couldn't just keep it as is.

 

In addition, you might want to consider using Soundex to match names with alternate spellings.