SQLite personal book database design critique

Question

I want to build/manage a database for my books and eventually my video games and movies. What are criticisms, critiques, and pointers before I start populating the tables?

BOOK (
    book_id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    title TEXT NOT NULL,
    active_ind INTEGER NOT NULL DEFAULT 1,
    read_ind INTEGER NOT NULL DEFAULT 0
)

AUTHOR (
    author_id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    name TEXT NOT NULL
)

FORMAT (
    format_id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    format TEXT NOT NULL 
)

ISBN (
    isbn_id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    isbn TEXT NOT NULL
)

BOOK_FORMAT (
    book_id INTEGER REFERENCES BOOK (book_id),
    format_id INTEGER REFERENCES FORMAT (format_id)
)

BOOK_AUTHOR (
    book_id INTEGER REFERENCES BOOK (book_id),
    author_id INTEGER REFERENCES AUTHOR (author_id)
)

BOOK_FORMAT_ISBN (
    book_id INTEGER REFERENCES BOOK (book_id),
    format_id INTEGER REFERENCES FORMAT (format_id),
    isbn_id INTEGER REFERENCES ISBN (isbn_id)
)

I'm iffy on the ISBN structure(s). I want to add a book's publication date but I don't know where.

Answer 1

My answers are a bit vague, because I'm not sure what your intent is in this project, whether it's just a hobby or a prelude to adding database expertise to your professional credentials.

I'll note in passing that many database systems, unlike SQLite, do not require NOT NULL on top of PRIMARY KEY. However SQLite documentation says that SQLite does. So you don't need to change things.

There appear to be two different relationships between book and format, and between book, format, and ISBN. I'm not sure what your intent is here, and I'm not a subject matter expert on ISBNs. What purpose is served by BOOK_FORMAT that is not also served by BOOK_FORMAT_ISBN?

You have no foreign keys (REFERENCES constraints) embedded in the primary tables. This is different from every database I ever designed, although, for all I know, it may be appropriate in your case. In general, relationships are expressed with junction tables, as you have done, when the relationships are many to many, or are ternary. If you ever need to express a many-to-one relationship, you will find it more convenient to embed it in an entity table than to create a separate table for the relationship.

Trial and error is an awfully expensive way to learn databases, unless you don't value your own time. While you don't need to master an entire textbook on theory before diving in, I recommend that you at least begin to learn the theory behind databases in parallel with your project. Databases are not complicated compared to other aspects of computing, but they are a little on the abstract side. My experience with programmers cutting over to databases is that they tend to think well in terms of concrete details, but somewhat poorly in terms of overall abstract structure. Your experience may be different.

Object oriented modeling is one of the more powerful abstract tools that many programmers acquire. Unfortunately, data modeling and object modeling often lead to quite different thought patterns and many programmers find data modeling difficult to master precisely because object modeling has been so useful to them.

Another way to help your learning is to look at the way other people have solved your same problem. There is an organization called Database Answers that offers a library of hundreds of sample database models. It's available here. If you navigate to "Libraries" I'm sure you'll find some examples that overlap your project. Take them with a grain of salt, however. One person's best practices do not always suit another person's project goals.

Finally, I'm going to point you to an answer I gave about 7 years ago to a question that may or may not be relevant to your quest. It's what every developer should know about databases.

Answer 2

if you want to add book's publication date than you create tbl_Publication table and modified your all table like below and please follow standerd to esaly industand all code here tbl is Represent this is table name

tbl_Book (
    Book_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    Title TEXT NOT NULL,
    Active_Ind INTEGER NOT NULL DEFAULT 1,
    Read_Ind INTEGER NOT NULL DEFAULT 0
)

tbl_Publication (
    Publication_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    Publication_Name TEXT NOT NULL
)

tbl_Author(
    Author_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    Author_Name TEXT NOT NULL
)

tbl_Format (
    Format_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    Format TEXT NOT NULL 
)

tbl_ISBN (
    ISBN_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    ISBN TEXT NOT NULL
)

tbl_Book_Full_Details(
    Book_FD_Id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
    Book_Id INTEGER REFERENCES tbl_Book(Book_Id),
    Author_Id INTEGER REFERENCES tbl_Author(Author_Id),
    Format_Id INTEGER REFERENCES tbl_Format (Format_Id),
    ISBN_Id INTEGER REFERENCES tbl_ISBN (ISBN_Id),
    Publication_Id INTEGER REFERENCES tbl_Publication (Publication_Id),
    Publication_Date DATETIME
)