Search code examples
databasesqlitedatabase-normalization

Normalisation issues regarding a relationship

I am looking for some guidance regarding a library database I am currently creating.

The Situation
I have an Accounts Table that stores all of the user's information.
I have a Books Table that stores all of the book's information.
I have a MyBooks Table that attempts to store the books that a user has taken out from the library.

The user can borrow many books and a book can be borrowed by many users (given that the book is free to be taken).

My Approach
Now, the issue is that I believe that my approach to solving this issue is not normalised.\

ERD I designed prior to implementing the database.

It leads to this within the table, which allows the table to store multiple books that belong to one user.

However, I have been told there should be a linking table somewhere because the BookID can just keep growing and seems that it could populate the table very fast and make it slower, though I'm not sure if that is true.

Here is my approach when I created the database using sqlite3 in Python 3 and it achieved the results I wanted, but at a possible cost of normalisation?

from sqlite3 import connect

conn = connect('LibrarySystem.db')
c = conn.cursor()


c.execute("""CREATE TABLE Accounts (
            user_id INTEGER PRIMARY KEY,
            email_address NVARCHAR(320) NOT NULL DEFAULT '',
            password VARCHAR(60) NOT NULL DEFAULT '',
            staff_mode INTEGER NOT NULL DEFAULT 0,
            my_booksID INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(my_booksID) REFERENCES MyBooks(my_bookID)
            )""")
conn.commit()


c.execute("""CREATE TABLE Books (
            bookID INTEGER PRIMARY KEY,
            title VARCHAR(100) NOT NULL DEFAULT '',
            author VARCHAR(100) NOT NULL DEFAULT '',
            genre VARCHAR(100) NOT NULL DEFAULT '',
            issued INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(genre) REFERENCES Genres(genre)
            )""")
conn.commit()

c.execute("""CREATE TABLE MyBooks (
            my_booksID INTEGER NOT NULL DEFAULT '',
            bookID INTEGER NOT NULL DEFAULT '',
            date_issued TIMESTAMP NOT NULL DEFAULT '',
            return_date TIMESTAMP NOT NULL DEFAULT '',
            FOREIGN KEY(bookID) REFERENCES Books(bookID)
            )""")
conn.commit()

Is there any normalisation issues regarding my approach?\

Solution

  • In the table Accounts there is a column my_booksID referencing my_booksID of MyBooks. Why?
    Do you plan to have a new row in Accounts for the same user every time they take a book?

    Instead, you should have a column user_id in MyBooks referencing user_id in Accounts.
    This way you make MyBooks the linking table between Accounts and MyBooks.

    When a user takes a book out from the library, you will add a new row in MyBooks with the user_id of the user and the bookID of the book.

    Also, in SQLite there are no VARCHAR and TIMESTAMP data types (check Datatypes In SQLite Version 3).
    In the case of VARCHAR use TEXT and for TIMESTAMP, if you want to store dates in the format YYYY-MM-DD (which is the only valid date format for SQLite) use TEXT, or if you want to store dates as unix timestamps use INTEGER.