Jump to content

How to handle pseudonyms and names variants in a person relation


This topic is 2048 days old. Please don't post here. Open a new topic instead.

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

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.

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.

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.

Link to post
Share on other sites

This topic is 2048 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Who Viewed the Topic

    1 member has viewed this topic:
    robyt 

×
×
  • Create New...

Important Information

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