very basic SQLite table design

Question

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.

Answer 1

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.

Answer 2

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.