model associations between movies, actors, directors... etc in CakePHP

I'm trying to figure out the best way to set up my tables/model associations.

I have a table of movies. Each Movie could have zero or many Actors, Directors, Publishers, Writers... etc.

I can set it up just fine if I use different tables/models for each type of person... but that seems unclean.

My thought is to use a Pe开发者_运维技巧ople model/table, with a HABTM to PeopleType and HABTM to Movie.

This would work just fine to link specific people to a movie, but... what about people like Clint Eastwood - he'd be a director for one movie, an actor for another, and maybe even both. How would the movie know which type it's referring to?

Just can't wrap my head around the best way to do this.

---

Another way you can do:

people_types (id, name)
people_details (id, first_name, last_name, ....)
people (id, person_details_id, person_type_id)
movies_people(id, person_id, movie_id) 
movies (id, title, ...)

---

As a person can be the director in one movie and the actor in another movie your approach of using 'people type' does not seems good as your people table would contain duplicate information for every person belonging to multiple types. I would create a table people_details and specific tables for every type directors, actors, publishers etc. with foreign keys to people_details table. directors, actors, publishers tables would also contain any columns specific only to the corresponding type of people.

To summarize, Actor belongsTo PersonDetails, Director belongsTo PersonDetails, Actor HABTM Movie, Director HABTM Movie.

---

In your join table: movies_people

You could add a extra field for something like "role". Where you would specific the persons function in that movie. Then you can add Client Eastwood into the join table twice, once for each role he has in the production of that movie.