Library Database Database Design

I have a library, and I want to transfer it from the old system to the new optimized one.

The old library was built using MS Access (mdb files), where each book is a file in itself. Each book consists of two tables, one for chapter titles and one for content. There is also one mdb file that contains all book names, page numbers for each book, author names, and other general information.

The new system database engine is MySQL, so I have three (possibly) options for storing books: 1- Save all the chapters in the chapters in one table, and all the books in another huge table. 2. Save all chapter chapters in one table, as well as the contents of each book in a table for this book. (so if I have 5 books, I will get 1 table for chapter titles and 5 for content). 3- Keep them as your old system design, each book has two tables, one for headings and one for it.

I do not prefer to use multiple databases, one database can do this job perfectly, like blogs hosted on wordpress.com (yap, they have a huge database and, of course, they use other methods, but I'm talking about DB system architecture )

There are over 500 books in this library, and the number is increasing.

What do you think?

+3
source share
2 answers

None of the above. There is no reason why you need to separate chapters from the contents of a chapter. But you need one table for the books themselves. The table structure is suggested here:

CREATE TABLE books (
  book_id     SERIAL PRIMARY KEY,
  title       VARCHAR(100) NOT NULL,
  published   DATE NOT NULL,
  isbn        VARCHAR(16) NOT NULL
  -- etc.
);

CREATE TABLE chapters (
  book_id     BIGINT UNSIGNED NOT NULL,
  chapter_id  SERIAL,
  chapter_num VARCHAR(10) NOT NULL,  -- varchar to allow chapter "VII."
  title       VARCHAR(100) NOT NULL,
  content     MEDIUMTEXT,
  PRIMARY KEY (book_id, chapter_id),
  FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Several thousand rows in the chapter table are trivial for a database such as MySQL.

+5
source

Here is my recommendation:

Table of books with primary key, title, ISBN, publisher, etc. Authors table with primary key, name and foreign key for books. Content table with primary key, chapter number, chapter title, content outline and foreign key for books.

BLOB CLOB . . .

Lucene , Google. .

+2

All Articles