CODEWITHSUNDEEP
sqlsql-serverdatabase
Tsafou
Tsafou

Reputation: 53

SQL movie table design & query

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

Upvotes: 0

Views: 1549

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 1

Onkel Toob
Onkel Toob

Reputation: 2212

  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.

Upvotes: 1

Related Questions