How can I store multiple values in SQL?

Question

I've a table which stores books:

CREATE TABLE `Books` (
  `book_id` int(250) NOT NULL,
  `book_name` varchar(100) NOT NULL,
  `book_author` varchar(10) NOT NULL,
  `book_genre` varchar(100) NOT NULL,
  `book_floor` int(5) NOT NULL,
  `book_shelf` int(255) NOT NULL,
  `book_amount` int(255) NOT NULL,
  `book_available` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to write a table of users which would have a column for books they borrowed, so it has to store all the information about the book from a Books table and it should be possible to store multiple books. How can I do that?

Look up the basic normal forms and (one|many)-to-(one|many) relationships, both fundamental aspects of database design. You don't really want to duplicate "all the information about the book", which would be horribly redundant - you want to specify which users have borrowed which books, by key, in a linking table, and nothing further. — underscore_d, Nov 06 '17 at 14:07
This answer / question could really help you [How to implement a many-to-many relationship?](https://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql) — Radu Gheorghiu, Nov 06 '17 at 14:07
Possible duplicate of [Storing multiple values for a single field in a database](https://stackoverflow.com/questions/10572987/storing-multiple-values-for-a-single-field-in-a-database) or https://stackoverflow.com/questions/24865310/how-to-store-multiple-values-in-single-field-in-sql-database or https://dba.stackexchange.com/questions/59594/how-to-store-multiple-value-in-column-in-sql-server or https://stackoverflow.com/questions/25236652/how-to-store-multiple-values-in-same-sql-column or probably tens or even hundreds of others. Did you try to search, at all? — underscore_d, Nov 06 '17 at 14:07

score 2 · Accepted Answer · answered Nov 06 '17 at 14:05

2

Use a third table borrowed books which would link books and users

answered Nov 06 '17 at 14:05

score 0 · Answer 2 · answered Nov 06 '17 at 14:13

You have a many to many relationship between users and books. You can't do this with just those 2 tables. You'll need a 'UserBooks' table. This table will have a primary key with user_id and book_id.

So basically, for every book a user has borrowed you'll have a row in this table mapping the user's id to the book id.

How can I store multiple values in SQL?

2 Answers2