I need to create a database for a simple website with films collection. I decided I need following columns in the table:

Click Here

I made some reading and after getting familiar wit normalization rulez I decided to divide this in four tables:

1st table "Basic info"with columns: movieID,titleID, directorID, yearID
Click Here

2nd table:"Artists ID" with colums: movieID, titleID, artist1_ID, artist2_ID,artist3_ID,artist4_ID
Click Here

3rd table "CategoryID" with columns: movieID, titleID, category1_ID, category2_ID, category3_ID, moodID
Click Here

4th table "DescriptionID" with columns: movieID, titleID, trailerID, descriptionID
Click Here

Im having problem with creation relations. I know it should be one-to-many relation but I dont know how to draw it in diagram. Can anybody help me with this please ? Also are tables above look alright ?

Thanks in advance

None of your images show a diagram, just a table layout. It's hard to guess how far you want to take this, but I'd suggest never to create columns named "artist1_ID, artist2_ID, artist3_ID, artist4_ID". This indicates that you need an intermediate table, because what do you do when you need more than four artists.

I'd suggest you start by describing what entities you want to store, e.g. movies, genres, actors. Based on that my choice would be something like this:

MOVIES        ACTORS   GENRES       MOVIES_ACTORS
------------  -------  -----------  --------------
Id (PK)       Id (PK)  Id (PK)      MovieId (FK)
Title         Name     Description  ActorId (FK)
Year
GenreId (FK)

Trailers and directors can be solved just like MOVIES_ACTORS because you can have more of them for a single movie.

Thanks a lot of the quick review. It will help me a lot.

Why did you use 2 foreign keys in movies_actors table ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.