0

Imagine I want to create a SQLite database of films, where the columns correspond to: title publication year, genre... How would you do it to store a list of actors in the database? Consider that:

  • Since the number of actors may vary from film to film, I cannot use one column per actor.
  • SQLite does not have a data type that corresponds to a list.
  • I want to use, for example "SELECT filmname FROM table WHERE X". Where X would be something that indicates if an actor is present in the film. I would like to be able to use a single or multiple actors on X

The best thing I though is to use a single string, like "actor1_actor2", and then apply "WHERE actorname LIKE %actor1". But that would allow me to filter films by only one actor.

Thanks!

lemon
  • 14,875
  • 6
  • 18
  • 38
Abel Gutiérrez
  • 159
  • 8

2 Answers2

1

You create tables movies, actors and a separate bridge table movies_actors for defining your many-to-many relationship (movie<->actor). The simplest form of the bridge table includes two columns, such as movie_id and actor_id, and two foreign keys: movies_actors.movie_id -> movies.id and movies_actors.actor_id -> actors.id.

PChemGuy
  • 1,582
  • 3
  • 6
  • 18
0

I would suggest, create a mapping separate table for the actress with a mapping key as Film's primary table. so that you can run all type of query operations on your table structure.

Sanket Chintal
  • 11
  • 1