CODEWITHSUNDEEP
sqldatabase-design
A Jackson
A Jackson

Reputation: 2856

Implementing a database of people that have alternative names and linking all names together

I'm creating a database for a small museum of commemorative pieces. This are mostly made for royal events but there are other areas as well. Therefore, most pieces are related to a person and it should be possible to search for all pieces about a particular person.

The problem I'm having is that in royalty, it is quite common that a person's name will change, sometime several times, throughout their life. It should be possible to search for them by any name, but bring up all the pieces under any name. The pieces should still be listed under the name that is written on.

Therefore, I need some way of storing all the name, while linking them together where relevant. Then being able to easily search for any name.

2 ways I have thought of are to have an Alternate Names table that has a foreign key back to the Person table, the problem then is that a search has to search in both tables and reference back to Person if necessary. I can't think of an easy to do that.

The other way would be to list all names in one table, and have another table that links them using 2 foreign keys.

Is there any easy way to search using either of these methods, or any better method for creating the database in the first place?

Thanks

Upvotes: 3

Views: 643

Answers (2)

Dan
Dan

Reputation: 2195

Since there's a one-to-many relationship between Person and Name, I would structure it as such:

  • Piece: PieceID, PersonID, Foo, ...
  • Person: PersonID, Bar, ...
  • Names: NameID, PersonID, Name, ...

Your query would then just be:

SELECT Foo, Bar, Name
FROM Names
    JOIN Person USING(PersonID)
    JOIN Piece USING(PersonID)
WHERE Names.Name LIKE '$query'

Upvotes: 3

maple_shaft
maple_shaft

Reputation: 10463

You sound like you are on the right track.

Tables:

  • Piece
  • Person
  • Pseudonym
  • PersonPsuedonym

The PersonPseudonyms table of course being a mapping table between Person and Pseudonym.

You should be able to easily join Person to a Pseudonym by joining Person to PersonPseudonym, then Pseudonym to PersonPseudonym, then filtering on Pseudonym for the name you are searching for.

Upvotes: 2

Related Questions