Designing a database schema for an online merchandise store involves several key tables to handle products, customers, orders, and potentially reviews and shipping details. Here’s a simplified schema that covers the basic entities and their relationships:
- Users
UserID
(Primary Key)FirstName
LastName
Email
PasswordHash
IsAdmin
(Boolean to identify if the user is an admin)
- Products
ProductID
(Primary Key)Name
Description
Price
StockQuantity
CategoryID
(Foreign Key)ImageURL
- Categories
CategoryID
(Primary Key)CategoryName
Description
- Orders
OrderID
(Primary Key)UserID
(Foreign Key)OrderDate
Status
TotalAmount
- OrderDetails
OrderDetailID
(Primary Key)OrderID
(Foreign Key)ProductID
(Foreign Key)Quantity
UnitPrice
- Reviews
ReviewID
(Primary Key)UserID
(Foreign Key)ProductID
(Foreign Key)Rating
Comment
ReviewDate
- ShippingDetails
ShippingID
(Primary Key)OrderID
(Foreign Key)AddressLine1
AddressLine2
City
State
Country
PostalCode
ShippingMethod
Cost
EstimatedDeliveryDate
Relationships:
- A
User
can have multipleOrders
. - An
Order
contains multipleOrderDetails
. - An
OrderDetail
corresponds to a singleProduct
. - A
Product
can belong to oneCategory
. - A
Product
can have multipleReviews
. - Each
Review
is written by a singleUser
. - An
Order
has oneShippingDetail
.
Notes:
- Products: Each product has a foreign key linking to its category. If you plan on supporting multiple images per product, you might want to have a separate
ProductImages
table. - OrderDetails: This is a junction table that contains the many-to-many relationship between orders and products, including the quantity of each product ordered and the price at which it was sold.
- Reviews: Reviews link users to products. You might want to include a timestamp for when the review was written.
- ShippingDetails: You might consider normalizing this table further by separating out addresses into their own table if you expect users to have multiple shipping addresses.
Here’s a simple ERD (Entity-Relationship Diagram) representing the schema:
+----------+ +---------+ +-----------+
| User | | Product | | Category |
+----------+ +---------+ +-----------+
| UserID |1 *| ProductID|1 *| CategoryID|
| FirstName|-----| Name |-----| CategoryName|
| LastName | | Description | | Description |
| Email | | Price | +-----------+
| PasswordHash| | StockQuantity|
| IsAdmin | | CategoryID |
+----------+ | ImageURL |
+---------+
|1
|
|*
+----------+
| Order |
+----------+
| OrderID |
| UserID |
| OrderDate|
| Status |
| TotalAmount|
+----------+
|1
|
|*
+------------+ 1 +---------+ 1
| OrderDetail|--------| Review |
+------------+ +---------+
| OrderDetailID |-----| ReviewID |
| OrderID |* 1| UserID |
| ProductID |------| ProductID|
| Quantity | | Rating |
| UnitPrice | | Comment |
+------------+ | ReviewDate|
+---------+
The cardinality between the entities is represented by 1
(one) and *
(many), indicating, for example, that one user can have many orders, but each order is linked to only one user.