0

I should start by saying that I am fairly new to SQL and databases. I read a lot and I have narrowed down my db design and questions. I will now try to explain.

So I am trying to build the 'typical' movie/film database (for SQL server).

Every film can have many actors, and every actor can participate in many films. The same for directors and genres. (many-to-many relationship with dbo.Films)

Film also has date and budget that are considered as one-to-one relationship (I put them in the dbo.Films).

Actors can have 1 fname, 1 lname and 0 or more email addresses. Directors have 1 fname and 1 lname. You can see my design in the image below (PK is the primary key).

See my db_design

1) How is my design and way of thinking so far?
2) How should I design the dbo.Emails table? If I am not mistaken this has an one-to-many relationship with actors.
3) Let's say I want to get all information I can (title, date, budget, actor(s), director(s), genre(s)) for a single movie, all in one row/line. I guess I have to use INNER JOIN. I tried the following but I got multiple rows of the same actor but different i.e. genre (in fact I think I get all combinations possible). I use Temp just to get rid of the (film) id column from the results. Is it too complicated or am I missing something?

IF OBJECT_ID('dbo.Temp', 'U') IS NOT NULL
DROP TABLE dbo.Temp

SELECT f.*, a.fname, a.lname, g.name INTO Temp
FROM [Video].[dbo].[films] f 
INNER JOIN [Video].[dbo].[film_actor] af
    ON f.id = af.film_id
INNER JOIN [Video].[dbo].[actors] a
    ON af.actor_id = a.id
INNER JOIN [Video].[dbo].[film_genre] gf
    ON f.id = gf.film_id
INNER JOIN [Video].[dbo].[genres] g
    ON g.id = gf.genre_id   

WHERE f.title = 'SomeMovie'

ALTER TABLE Temp
DROP COLUMN id
/* Get results and drop temp table */
SELECT * FROM Temp
DROP TABLE Temp
Tsafou
  • 43
  • 8
  • You are thinking in relational way. Don't worry. Your database looks OK. You can add ActorEmails(ID, ActorID, Email). Also add PKs in Film_Genres, Film_Actors and Film_Directors. – Giorgi Nakeuri Mar 31 '15 at 13:12
  • 1) Should I add PKs for both film_id AND genre_id in Film_Genre? What about Film_Actors and Film_Directors? What is the purpose of the PK here? 2) Could you be more specific regarding the .Persons table ? If I add .Persons and have id, fname, lname, email how am I going to tell who is actor and who director? – Tsafou Mar 31 '15 at 14:16

2 Answers2

1
  1. With one exception the design seems fine to me. I would, however, rather have a table dbo.Person or something like that, because actors and directors both share some pretty person-ish properties (names, for example). And what about actors who also direct and the other way around? You wouldn't want two database entries for those.
  2. Regarding the ´dbo.Emails´ table I don't see any problems. I'd expect a one-to-many relationship to the aforementioned table of persons. Therefore, the table would probably have an ´Id´ column, a ´PersonId´ and the email itself.
  3. Simply with JOIN statements you won't be able to retrieve all the information within one row because a JOIN will give you one row for each matching combination of two resultsets. So a movie starring ten actors will then result in ten rows. If that movie belongs to two genres you'll end up with twenty rows, and so on. There are ways to get all names of the actors within one column (comma-seperated, for example), but usually this is not what one would want. It depends on your application or use case, though.
Onkel Toob
  • 2,152
  • 1
  • 17
  • 25
  • 1. One can add third table Persons and Change Actors and Directors so they contain link to Person. Or make just Person table with PersonTypes table. But I prefer first. – Giorgi Nakeuri Mar 31 '15 at 13:20
  • @papatoob Can you explain the dbo.Person. Would you have Person.id, Person.fname, Person.lname, Person.email? What about the Film.Actor and Film_Director tables? Keep or delete them? – Tsafou Mar 31 '15 at 14:20
  • @tsafou `dbo.Person` would have Id and name columns; if there's only one email, then that would go there, too. For multiple emails you'd need a different table (see above). The `dbo.FilmActors` and `dbo.FilmDirector` tables will still be needed, but they'd have a `PersonId` column, since there won't be specific Actor-IDs and Director-IDs. – Onkel Toob Apr 01 '15 at 07:50
  • A last maybe stupid question. On the UI, I plan to have a form where the user can enter the details of the movie, the actors etc. The ID of the dbo.Films will have to be sent to every table that uses it (i.e. dbo.Films, dbo. FilmPersons, dbo.FilmGenres etc.)? Or is it possible that all tables 'take' this key from one Table through a connection? (the same applies for all other columns?) – Tsafou Apr 01 '15 at 08:36
  • The ID actually **is** the connection between the tables, so yes, you will have to use it almost everywhere. – Onkel Toob Apr 01 '15 at 09:43
1

This is big topic indeed. There whole books on database normalization.

  1. Genres(ID(PK), Name) is OK.
  2. Films(ID(PK), Title, Date, Budget) is OK

  3. FilmGenres(FilmID, GenreID) here you can do the following

    a. Add composite PK FilmGenres(FilmID(PK), GenreID(PK))

    b. Add surrogate PK FilmGenres(ID(PK), FilmID, GenreID) and also unique index on FilmID, GenreID FilmGenres(ID(PK), FilmID(U), GenreID(U)). Here you want uniquness on combination FilmID and GenreID. I guess you understand why.

  4. Actors and Directors. Here you can add Persons(ID(PK), FirstName, LastName) and link this table in Actors(PersonID(PK,FK), ...) and Directors(PersonID(PK,FK), ...)

  5. FilmActors(FilmID, ActorID) - here you apply bullet 3.

  6. FilmDirectors(FilmID, DirectorID) - here you apply bullet 3.

  7. Add PersonEmails(PersonID, EmailID) - here you apply bullet 3.

Bullet 4, 5, 6 can change if:

  1. PersonTypes(ID(PK), Name) : director, actor, producer...

    Persons(ID(PK), FirstName, LastName)

    PersonTypesPersons(PersonID, PersonTypeID) - here you apply bullet 3.

  2. FilmActors - don't need anymore.

  3. FilmDirectors - don't need anymore.

  4. Add PersonEmails(PersonID, EmailID) - here you apply bullet 3.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • According to your design, should Directors have only PersonID(PK, FK) or some more info like FirstName, LastName again? – Tsafou Mar 31 '15 at 19:15
  • No FirstName, LastName will go to Persons table. Directors will have its own info related to directors, actor will have its own relating to actors – Giorgi Nakeuri Mar 31 '15 at 19:18
  • Ok I see. I guess for my design I might not need that. Or do I need it only for actors and not directors? (actors have only FirstName, LastName and email, and directors have only FirstName, LastName). Sorry but its my first time and I am confused. – Tsafou Mar 31 '15 at 20:01
  • @Tsafou, then stick to second design withown directors and actors tables – Giorgi Nakeuri Mar 31 '15 at 20:05