在mysql数据库中存储未知大小的数组

Question

Hi I have in mysql database a datetime value (2015-01-01 12-54-32) which I use to know the date in which the library subscriber reserved a book it is easy to store one datetime value in database but the problem is i don't know how many books the subscriber will reserve from the library could be 1 or could be 5 , the question is how do i store it in database as array ?

Answer 1

The correct way to store this in a database is to use a junction table. Something like this:

create table BookReservations (
    BookReservationId int auto_increment primary key,
    SubscriberId int not null,
    BookId int not null,
    ReservationDate date not null,
    constraint fk_bookreservations_subscriberid foreign key (SubscriberId) references Subscribers(SubscriberId),
    constraint fk_bookreservations_bookid foreign key (BookId) references Books(BookId)
);

(Clearly, this just suggests names for entities and columns not mentioned in the question.)

Note that this table defines the foreign key relationships among the tables as well as storing the data. This allows the database to ensure relational integrity. MySQL does not (currently) allow such definitions for string or JSON objects.