Simple online bookstore database design

Not many requirements. User ID, account password, ID, title, author, etc. in the table. How do I save which user bought which book? Should I add a username to the library and build a relationship diagram? Or was he going to build a table to save it? I just don't understand how to make sure that the book A buys is the book A buys, and not all users buy it. How do I design this relationship diagram? There's no need to classify the books or anything.

Answer 1

The database design of the online bookstore should include the following components:

1. Item Information Table (Item) -Store the basic information of the item such as title, author, price, description, classification, etc.

2. Order Information Table ( Order) -Store the order information of the customer, including the order number, customer name, order status, order items, etc.

3. Stock-Storage of stock information, including item number, stock quantity, purchase date, supplier, etc.

4. Order Management-used to record all orders created, modified, deleted, and the status of the order (completed, in progress, not processed, etc.).

5. Customer Information Form-used to record the basic information of the customer, such as name, email address, telephone number, etc.

6. The payment information table-used to record all payment methods such as credit card, AliPay, Weixin Pay, etc.

7. The supplier information sheet-used to record the basic information of the supplier, such as company name, address, contact information, etc.

The following is a simple online bookstore database design example. The product information table, order information table, and inventory information table already include some basic data fields:

```
--Item Information Form (Product)
CREATE TABLE Product (
Product_ID INT PRIMARY KEY
Product_Name VARCHAR(50) NOT NULL
Product_Price DECIMAL(10 2) NOT NULL
Product_Description TEXT NOT NULL
Product_category VARCHAR(20) NOT NULL
FOREIGN KEY (Product_ID) REFERENCES Product(ID)
);

--Order Information Form (Order)
CREATE TABLE Order (
Order_ID INT PRIMARY KEY
Customer_Name VARCHAR(50) NOT NULL
Order_Status VARCHAR(20) NOT NULL
Order_Product VARCHAR(50) NOT NULL
Order_Time TIMESTAMP NOT NULL
FOREIGN KEY (Order_ID) REFERENCES Order(ID)
);

--Storage Information Form (Stock)
CREATE TABLE Stock (
Stock_ID INT PRIMARY KEY
_
Arrival_Date Date NOT Empty
<strong></strong><strong></strong>
FOREIGN KEY (Stock_ID) REFERENCES Stock(ID)
);

--Order Management
CREATE TABLE Order Management (
Order_ID INT NOT NULL
Order_'s status VarCHAR(20) NOT nil
Order_'s method VARCHAR(20) NOT nil
FOREIGN KEY (Order_ID) REFERENCES Order(ID)
);

--Customer Information Form
CREATE TABLE Customer (
Customer_ID INT NOT NULL
Customer_Name VARCHAR(50) NOT NULL
Customer_Email VARCHAR(100) NOT NULL
Customer_Phone VARCHAR(20) NOT NULL
FOREIGN KEY (Customer_ID) REFERENCES Customer(ID)
);

--payment information form (Payment)
CREATE TABLE Payment (
Payment_ID INT NOT NULL
Payment_Name VARCHAR(50) NOT NULL
Payment_Type VARCHAR(20) NOT NULL
Payment_Time TIMESTAMP NOT NULL
FOREIGN KEY (Payment_ID) REFERENCES Payment(ID)
);
```

In the above design, each table has a unique ID as the primary key to ensure the uniqueness and integrity of the data. At the same time, in order to support multi-currency transactions, the payment information table also included a payment type field to distinguish different payment methods.