CODEWITHSUNDEEP
mysqldatabase
test
test

Reputation: 18198

MySQL - Storing IDs as an array in a table

Let's say I have two tables on MySQL 5.7

Film
---
ID
name
location
year
user_id
actors

Actor
---
ID
name
born
location

Then I want to link each actor to a film, so each film entry would have actors as an array like [5, 2, 12] and on.

Now, that's one way, I have been told. Is this the appropriate way? Is this right? Wrong?

Upvotes: 5

Views: 7098

Answers (4)

a20
a20

Reputation: 5651

You can use MySql JSON field to store arrays or lists that can still be indexed, queried by the DB engine.

Upvotes: 1

DerHerrGammler
DerHerrGammler

Reputation: 332

enter image description hereIf I understand you correctly then:

You have to create a Foreign Key in your actors table which contains the film id. But there you can only take ONE film per actor.

If you create a table BETWEEN these tables you can access both tables and combine them with join. So every actor can take place in more than only one film.

Never save an Array in your Database, because you can't access this array with select commands.

Upvotes: 9

nick
nick

Reputation: 891

Don't use comma delimited values in a table. Rather than have Actors and Films in the Films table, make another table called film_actors or whatever and if you need a table for actor info make an Actors table as well. Then in film actors make a new entry for each actor in the films. It's much less taxing to search these fewer columns and a simple int than a whole row of other information plus parse commas. A sample of some data from film_actors should look like the following:

film : 1, actor : 2 , 
film : 1, actor : 4, 
film : 2, actor : 2

Searching through csv columns is a lot more taxing than doing a search of all films where film = x and actor = y.

Upvotes: 2

Juan
Juan

Reputation: 5589

In relational databases instead of storing an array of ids a field, you should store a record for each id in a separate related table.

In this specific case, you can have a Film with many actors, and also each actor of this specific film could also work in other different films, so the relation is many to many.

To model this relation you actually need a third table that would hold the ids of the related actors and films the work in.

Like this:

Film

ID
name
location
year
user_id

Actor

ID
name
born
location

ActorInFilm

ActorID
FilmID

Upvotes: 2

Related Questions