-1

I'm doing a hobby project of creating my own movie database with ratings, genres, release dates and what actors are linked to what movies (to in the future distinguise movies from actors search).

I have a table containing Movies with some attributes where one attribute at the moment should be actors included in that movie. I know I can't store a list of strings (names of actors) in a cell for each row (movie) in the table. But what is best practice here? Create another table with MovieActors and link what movies each are connected to? What if the actor is connected to more movies?

I'm running Microsoft SQL Server Management Studio in my own localhost database and have just set up the Movie table where I found out I might get this problem.

I expect to get a table containing movies, and in the future be able to search movies, find their attributes and find out what actors are connected to that movie. Also be able to filter movies depending on what actor I've chosen.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Christian Dahlberg
  • 17
  • 1
  • Hi. How could this not be a duplicate? Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. But any justified answer is rewriting any introduction to database design. So it is also too broad. Ask questions where you are stuck--including reading a relevant resource--and give the context and how you are stuck. – philipxy Mar 29 '19 at 02:20
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Mar 29 '19 at 02:22
  • Time to read a published academic textbook on information modeling & database design. (Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design.) – philipxy Mar 29 '19 at 02:24

2 Answers2

1

What you have is a many-to-many relationship. When building a relational database, this would consist of a Movie Table, an Actor Table. and then a "junction" or "join" table, which houses a foreign key to both Movie and Actor. You can then join using this "junction" table to get all actors who were in a specific movie, or vice versa, get all movies a specific actor was in, etc.

Example Diagram:

Table Movie                  Table ActorMovie            Table Actor
MovieId | MovieName          MovieId | ActorId           ActorId | ActorName
1         jaws               1         1                 1         Guy1
2         star wars          1         2                 2         Guy2
                             2         1
Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
  • Thank you a lot! I indeed should've pictured my idea in a model before executing the code. It's obviously a many-to-many relationship which requires a table itself. – Christian Dahlberg Mar 28 '19 at 19:16
  • @ChristianDahlberg You're welcome. I'm glad to help, added a bit of a diagram as well. I'm guilty of coding without planning from time to time, but I try to avoid that at all costs anymore. It is very useful to draw out diagrams of things before execution as it helps to think logically and build better applications and database structures. – Ryan Wilson Mar 28 '19 at 19:17
  • Understandable, it's easy to jump onto the more fun stuff! Now I just have to figure out how to get the connection string to my localhost SSMS database. – Christian Dahlberg Mar 28 '19 at 19:23
  • @ChristianDahlberg (https://stackoverflow.com/questions/10479763/how-to-get-the-connection-string-from-a-database/10480011) – Ryan Wilson Mar 28 '19 at 19:34
0

As you suggested, the usual practice is to have an intermediate MovieActors table, which links what movies have which actors.

Like this:

Movie   Actor
1       2
1       3
1       4
2       2
2       5

You'll link the IDs with their respective tables via foreign keys (FK).

fhcimolin
  • 616
  • 1
  • 8
  • 27