Library Database System Design

Question

Hello,

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

The old library was built using MS Access (mdb files), where each book is a file by itself. Each book consists of two tables, one for chapters titles and the second for contents. There is also one mdb file that contains all books names, pages number for each book, authors names and other general info.

The new system database engine is MySQL, so I have three (maybe) possibilities for storing books: 1- Store all the books chapters titles in one table, and all books contents in another huge table. 2- Store all books chapters titles in one table, and every book's content in a table for this book. (so if I have 5 books, I'll get 1 table for chapters titles and 5 for content). 3- Store them as the old system's design, each book has two tables, one for it's chapters titles and the second for it's content.

I don't prefer to use multiple databases, one database can do the job perfectly as the blogs hosted by wordpress.com (yap, they have huge database and of course they use other techniques, but I'm talking about DB system architecture).

There are more than 500 books in that library and the number is increasing.

What do you think?

Answer 1

None of the above. There's no reason you have to separate chapter titles from chapter content. But you need one table for the book titles themselves. Here's a suggested table structure:

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)
);

A few thousand rows in the chapters table is trivial for a database like MySQL.

Answer 2

Here's my recommendation:

Books table with primary key, title, ISBN, publisher, etc. Authors table with primary key, name, and foreign key to Books. Contents table with primary key, chapter number, chapter title, path to contents, and a foreign key to Books.

I wouldn't store the chapter contents as a BLOB or CLOB in the database. You can't search in them. Better to keep them in the file system and just store a relative or absolute path to the file in the database.

You can use Lucene to index the contents to allow Google-like searches of the content. That would be an improvement over your current system.