Insert multiple values into a column on MySQL

Question

I'm doing a library management system and I created a table named BOOK with the column 'GENRE'.

A book might have multiple genre. For example fiction, romance, suspense.

What is the best way to insert this multiples tags into a single book? Should I insert the entire row with the others columns like title, author, publisher...?

Tks.

if you are asking this question, I feel that you should break it down into individual columns (genre1, genre2, etc). You could put them all in one then split them up as needed as a more advanced option. — Mech, Feb 11 '20 at 02:35
Well, if you're planning to select books by genre, see answer below — T.S., Feb 11 '20 at 02:51

score 1 · Answer 1 · answered Feb 11 '20 at 02:37

You're describing a multi-valued attribute. That is, a book can have multiple genres.

The way to do this in a relational database like MySQL is create another table. For example, we could call it BOOK_GENRE.

CREATE TABLE BOOK_GENRE (
  book_id INT NOT NULL,
  genre_id INT NOT NULL,
  PRIMARY KEY (book_id, genre_id),
  FOREIGN KEY (book_id) REFERENCES BOOK(book_id),
  FOREIGN KEY (genre_id) REFERENCES GENRE(genre_id)
);

For each book, there may be one or more rows in this new table. Each row has one pair of book with one of its genres.

This allows you to insert any number of genres you want. It allows you to search for books that have a specific genre. And many other types of queries.

This is the best strength of a relational database: data is represented by sets of rows, and you can keep adding rows.

It's much better than trying to stuff many values into one column!

You might also like my answer to Is storing a delimited list in a database column really that bad?

Insert multiple values into a column on MySQL

1 Answers1