如何使用MySQL中的关系将文本字符串批量转换为唯一ID

Question

I have a movie database I am working on and before I start working on the php side I want to make sure the database is solid.

As I have mass imported data scraped from the web currently my results in the columns for genre and actors are in text strings. I want to convert them to unique ids and create relationship tables.

Essentially how it is now:

Movie Table

Movie ID - Movie name - Genres - Actors
1        - Inception  - Sci Fi - Leonardo Di Caprio, Ellen Page

How I want it:

Movie Table

Movie ID - Movie Name
1        - Inception

Genre Table

Genre ID - Genre Name
1        - Sci Fi

Actor Table

Actor ID - Actor Name
1        - Leonardo Di Caprio
2        - Ellen Page

Genre Relationships Table

Movie ID - Genre ID
1        - 1

Actor Relationships Table

Movie ID - Actor ID
1        - 1
1        - 2

If it was just the genres then I could do this by hand but as there are thousands of movies and actors I am struggling to come up with a simple approach to convert all this data.

I have a csv dump of all the data and figure it could be done by using a php script to import it in this format or I don't know if it's possible to run SQL commands to sort the data this way (the database has over 200,000 movies).

Any hints or ideas on how to accomplish this would be much appreciated!

Answer 1

Something like this will sort of work:

For each record
    Do 
       Select from genre table using genre string to get genre ID
       If select did not return ID, INSERT new genre string to add new genre ID
    While Select did not return ID
    For each actor
       Do 
         Select from genre table using actor string to get actor ID
         If select did not return ID, INSERT new actor string to add new actor ID
       While Select did not return ID
  ...

But there will be problems:

• Movies with the same name
• Different spellings of genre names (sf, sci fi, science fiction)
• Different spellings of actor names. You'll see in IMDB actors might be Mike or Michael, with or without a middle initial, etc. and women might use their married name in some movies but not others
• Actors with the same name

To fix that you'd need access to some existing database where you can get the same ID code for any variation of an actor's name, for a genre name, and for movies when supplying an actor list.