Integrate Full cast and crew in a Movie Database

database-design

I'd like to display full cast and crew from my movie database and was wondering if the structure below is OK. Basically, I want something like this https://www.themoviedb.org/movie/284052-doctor-strange/cast

[movies]
id,
name

[people]
id,
name

[jobs]
id,
name (ex: Director, Writer)

[casts]
person_id,
movie_id,
character_name

[crew]
person_id,
movie_id,
job_id

Best Answer

This looks good to me.

You have correctly defined 3 entities: Movies, Jobs, and People

You have correctly defined 2 associative entities: Cast & Crew

Your entities are independent of each other and the associative entities handle the many-to-many relationship...ie many People can work on many Movies.

Related Solutions

Drawing an entity-relationship diagram for a movie sales business domain

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.

Mysql – How to integrate a new system to a design scheme I previously created

Would it not solve your problem to redefine the GRADE_LEVEL table such that it contains either the grade of a course or the level of the course (which ever applies)?

The rows in GRADE_LEVEL could contain values such as:

GRADE 7
GRADE 8
GRADE 9
GRADE 10
GRADE 11
GRADE 12
LEVEL 1
LEVEL 2
LEVEL 3

You might consider adding a partitioning attribute to the GRADE_LEVEL table that would indicate which educational system is being applied. This may be helpful for some of your application logic that might need to apply different processing depending on which system is in use for a given COURSE.

Best Answer

Related Solutions

Drawing an entity-relationship diagram for a movie sales business domain

Mysql – How to integrate a new system to a design scheme I previously created

Related Question