(Source Code and other stuffsWill be updated)

Foreword

I am creating "BookStore" ERD, I will start from the basic structures, It will be 4 domains below. This document will be about Junction table that should be used for Many-to-Many relationship, which can hold foriegn keys that reference to the primary keys of related tables. This will help to manage datas, without this, you can encounter several issues. (Be cautious about making relationships between Many and Many.)

BookStore ERD Basic Structures

Relationships (Member / Cart / Order / Book)

Member 1 : Cart 1

• 1 member can have 1 cart.

Member 1 : Order N

• 1 member can place multiple orders

Cart 1 : Order N

• 1 cart can lead to multiple orders

Order N : Book N

• 1 order can include multiple books.

• 1 book can be part of multiple orders.

• Many-to-Many Relationship managed by OrderBook intermediate table.

Cart N : Book N

• 1 Cart can contain multiple books

• 1 Book can appear in multiple carts, (If multiple customers are adding same book to their cart, these are perspectives of each book and cart.)

• Many-to-Many Relationship managed by CartItem intermediate table.

In conclusion,

We need Intermidiate Table for N : N OrderBook & CartItem

ERD link : https://www.erdcloud.com/d/xATnciRRJrPzGitM7

Junction Table

Junction Table

• It's also called Join table or associative table).

• It can hold foreign keys that reference the primary keys of the related tables.

• It's needed to manage Many-to-Many relationship.

Why do we need junction table?

It's for managing many-to-many relationship in a relational database.

It ensures "data normalization, integrity, flexibility, efficient querying."

1. Data Normalization

   • Prevents duplicated data across multiple places

   • Avoids data inconsistency and redundanccy

2. Data Integerity

   • Uses foreign keys to maintain data integerity

   • Ensures that related records exist in the reference tables.

     -> related records is kind of entries that include foreign key, which refers to the primary key in another table.

3. Flexibility

   • It can add additional attributes to the relationship, such as 'Quantity' in the case of books in orders.

4. Efficient Queries

   • It can facilitate

     "finding all books" in a particular order

     (ex)OrderId 1 -> Book 1 & 2 & 3)

     "querying all orders that include a specific book

     (ex)find the order that include Book 3)

Example Use Case

OrderId: 1, BookID: 101, Quantity: 2

OrderId: 1, BookID: 102, Quantity: 1

\=> this structure clearly shows below

• Order 1 contains two copies of Book 101

• Order 1 contains one copy of Book 102

+Example Schema (SQL, just for Reference)

Member Table

CREATE TABLE `Member` (
    `MemberID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `Password` VARCHAR(255) NOT NULL,
    `Name` VARCHAR(255) NOT NULL,
    `Email` VARCHAR(255) UNIQUE NOT NULL,
    `Phone` VARCHAR(20),
    `Address` VARCHAR(255),
    `CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `UpdatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);

Cart Table

Add a unique constraint on MemberID if a member can only have one cart:

CREATE TABLE `Cart` (
    `CartID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `MemberID` BIGINT UNIQUE,  -- A member can have only one cart
    FOREIGN KEY (`MemberID`) REFERENCES `Member`(`MemberID`)
);

Book Table

sqlCopy codeCREATE TABLE `Book` (
    `BookID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `Title` VARCHAR(255) NOT NULL,
    `Author` VARCHAR(255) NOT NULL,
    `ISBN` VARCHAR(13) UNIQUE NOT NULL,
    `Price` DECIMAL(10, 2) NOT NULL,
    `PublicationDate` DATE,
    `Publisher` VARCHAR(255),
    `Description` TEXT,
    `Quantity` BIGINT NOT NULL,  -- Stock available
    `CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `UpdatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Order Table

sqlCopy codeCREATE TABLE `Order` (
    `OrderID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `OrderDate` DATETIME NOT NULL,
    `CartID` BIGINT,
    FOREIGN KEY (`CartID`) REFERENCES `Cart`(`CartID`),
    `MemberID` BIGINT,
    FOREIGN KEY (`MemberID`) REFERENCES `Member`(`MemberID`)
);

OrderBook Table

Include Quantity to reflect how many of each book are ordered:

CREATE TABLE `OrderBook` (
    `OrderID` BIGINT,
    `BookID` BIGINT,
    `Quantity` BIGINT NOT NULL,  -- Quantity of the book in this order
    PRIMARY KEY (`OrderID`, `BookID`),
    FOREIGN KEY (`OrderID`) REFERENCES `Order`(`OrderID`),
    FOREIGN KEY (`BookID`) REFERENCES `Book`(`BookID`)
);

CartItem Table

Include Quantity to reflect how many of each book are in the cart:

CREATE TABLE `CartItem` (
    `CartItemID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `CartID` BIGINT,
    FOREIGN KEY (`CartID`) REFERENCES `Cart`(`CartID`),
    `BookID` BIGINT,
    FOREIGN KEY (`BookID`) REFERENCES `Book`(`BookID`),
    `Quantity` BIGINT NOT NULL  -- Quantity of the book in this cart
);

`` -> Order

:Orderis considered a reserved word in SQL, which can cause conflicts when used as an identifier (ID), so we enclose Order with `` like this : Order

('Order', this ensures database can interpret this correctly.)

PRIMARY KEY

:BIGINT can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

INT can store values from -2,147,483,648 to 2,147,483,647.

\=> If you anticipate your application needs scalability, you'd better to use BIGINT, frankly, I don't think my application needs it, but I set this for common practice.

Price

DECIMAL(p, s): DECIMAL(10, 2) can store values up to 99999999.99 This is ideal for financial data where precision is crucial.

Address

:TEXT can store up to 65,535 characters (capacity)

VARCHAR(255) can store up to 255 characters,

I think 255 characters would be enough for my current service. (It should be changeable depending on your requirements.)

---

★★★ Composite Primary Key For Junction Table.

Primary Key

• Normally a table can have only one primary key, which uniquely identifies each row in that table.

Simple Primary Key : can be a single column.

Composite Primary Key : can be a combination of multiple columns.

Composite Primary Key in OrderBook Table

Consider an OrderBook table with the following entries:

• Order 1: Contains BookID 101 and 102.

• Order 2: Contains BookID 101 and 103.

Uniqueness : The composite key ensures the combination of OrderId and BookId is unique within the table.

• Thus, within a single order, each book can only appear only once.

  (it's not related to Book's Qty within a single order)

Flexibility

• One book can be part of many orders (from many customers).

• Each order can contain multiple books.

• This can allow you to track

  ->which books are included in which orders + how many of each book are ordered.

In a nutshell,

• One Order : can include multiple books.

• One Book : Can appear in multiple orders.

This is because a junction table usually needs to ensure uniqueness across multiple columns that together form a unique values (pair or combination.)

CartItem Table

Consider the following entries in the CartItem table:

• Explanation:

  1. CartID 1:

     • BookID 101: 2 copies

     • BookID 102: 1 copy

  2. CartID 2:

     • BookID 101: 1 copy

     • BookID 103: 3 copies

Unique Identification

• Ensures each cart item is uniquely identifiable.

Flexibility

• This simplifies indexing and data management, allowing for easy updates and deletions.

Foreign Keys

• This establishes relationships with the 'cart' and 'book' tables, which can lead to them respectively.

Quantity Tracking

• Manage the number of each book in the cart.

---

Book Quantity Tracking

Even though Book has Book's Qty, Why do i need to add Quantity to Junction Table(Join Table) : OrderBook & CartItem.

Why Quantity For 3 each tables ?

• Book Table's QTY : to check stock available.

• OrderBook Table's QTY: Represents how many of each book are part of a particular order. This table needs a Quantity field to store how many copies of each book are ordered.

• CartItem Table's QTY: Represents how many copies of a book are added to a cart. This table also needs a Quantity field to track the number of items in the cart.

Understanding Quantity Tracking

1. Book Table’s Quantity: Represents the total stock available. This quantity is updated as books are sold or added to inventory.

2. Within Transaction(OrderBook and CartItem): Track how many books are associated with each order or cart. They need to store Quantity because this represents the quantity of books in a specific transaction, not the overall stock.

---

Source Code (Will be updated)