Jump to content
Sign in to follow this  
DaCo

How to handle pseudonyms and names variants in a person relation

Recommended Posts

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)?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.