How can I store multiple values in SQL? [duplicate]

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
– underscore_d, Commented Nov 6, 2017 at 14:07
This answer / question could really help you How to implement a many-to-many relationship? — Radu Gheorghiu
– Radu Gheorghiu, Commented Nov 6, 2017 at 14:07
Possible duplicate of Storing multiple values for a single field in a database or stackoverflow.com/questions/24865310/… or dba.stackexchange.com/questions/59594/… or stackoverflow.com/questions/25236652/… or probably tens or even hundreds of others. Did you try to search, at all? — underscore_d
– underscore_d, Commented Nov 6, 2017 at 14:07

user7191279 · Accepted Answer · 2017-11-06 14:05:12Z

2

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

answered Nov 6, 2017 at 14:05

user7191279

Sign up to request clarification or add additional context in comments.

Comments

user1751825 · Accepted Answer · 2017-11-06 14:13:02Z

1

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.

answered Nov 6, 2017 at 14:13

user1751825

4,3375 gold badges37 silver badges74 bronze badges

Collectives™ on Stack Overflow

How can I store multiple values in SQL? [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related