CODEWITHSUNDEEP
sqliterelational-database
user2880107
user2880107

Reputation: 15

very basic SQLite table design

I don't understand the fundamental set-up of tables in SQLite.

Let's say I have three tables:

Studio Table
studioID  studioName
--------  --------
1         Lucasfilm
2         Amblin Entertainment
3         Zoetrope Studios

Movie Table
movieID   movieName        studioID    actorID
--------  --------        --------    --------
1         Star Wars            1          1
2         Indiana Jones        1          1
3         Back to the Future   2          3
4         Apocalypse Now       3          2

Actor Table
actorID  lastName     firstName
-------- --------     --------
1        Ford         Harrison
2        Fishburne    Laurence
3        Lloyd        Christopher

This structure doesn't allow for the fact that Harrison Ford and Laurence Fishburne were both in Apocalypse Now. What would be a conventional way to represent Ford and Fishburne acting in the same movie using similar tables? Sorry for the newbie question.

Upvotes: 1

Views: 152

Answers (2)

Jonysuise
Jonysuise

Reputation: 1830

Studio 1 : N Movies -> One table for Studios, one for movies and add a FK from studio in movies.

Movies N : M Actors -> One table for actors, and another that links Movies and actors.

Upvotes: 0

juergen d
juergen d

Reputation: 204766

What would be a conventional way to represent both being in the same movie using similar tables?

There is none.

You need another table: cast

cast table
---------
movieID
actorID

Example data:

movieID | actorID
 4      | 1
 4      | 2

And maybe there should even be another column in the cast table: roleName. The same actor could have multiple roles in one movie.

Upvotes: 1

Related Questions