Table User {
id INT [pk, not null]
username VARCHAR(255) [not null]
email VARCHAR(255) [not null]
password VARCHAR(255) [not null]
}
Table ProductCategory {
id INT [pk, not null]
name VARCHAR(255) [not null]
description VARCHAR(255)
}
Table Product {
id INT [pk, not null]
name VARCHAR(255) [not null]
description VARCHAR(255)
inventoryId INT [not null]
price DECIMAL(10,2) [not null]
discountId INT [not null]
categoryId INT [not null]
}
Table ProductInventory {
id INT [pk, not null]
sku VARCHAR(255) [not null]
quantity INT [not null]
}
Table Discount {
id INT [pk, not null]
name VARCHAR(255) [not null]
description VARCHAR(255)
discountPercentage DECIMAL(10,2) [not null]
active BOOLEAN [not null, default: true]
}
Table Order {
id INT [pk, not null]
userId INT [not null]
createdAt TIMESTAMP [not null, default: `current_timestamp`]
modifiedAt TIMESTAMP [not null, default: `current_timestamp`]
}
Table OrderItem {
id INT [pk, not null]
orderId INT [not null, fk: Order.id]
productId INT [not null, fk: Product.id]
quantity INT [not null]
unitPrice DECIMAL(10,2) [not null]
}
Table OrderDetails {
id INT [pk, not null]
orderId INT [not null, fk: Order.id]
userId INT [not null, fk: User.id]
shippingAddress VARCHAR(255) [not null]
billingAddress VARCHAR(255) [not null]
shippingMethod VARCHAR(255) [not null]
paymentMethod VARCHAR(255) [not null]
totalAmount DECIMAL(10,2) [not null]
}
Table UserPayment {
id INT [pk, not null]
userId INT [not null, fk: User.id]
paymentType VARCHAR(255) [not null]
provider VARCHAR(255) [not null]
accountNo VARCHAR(255) [not null]
expiry DATE [not null]
}
Table PaymentDetails {
id INT [pk, not null]
orderId INT [not null, fk: Order.id]
paymentType VARCHAR(255) [not null]
paymentProcessor VARCHAR(255) [not null]
transactionId VARCHAR(255) [not null]
amount DECIMAL(10,2) [not null]
}
Table Shipment {
id INT [pk, not null]
orderId INT [not null, fk: Order.id]
trackingNumber VARCHAR(255) [not null]
shippingCarrier VARCHAR(255) [not null]
estimatedDeliveryDate DATE [not null]
}
Ref: User.id < UserPayment.userId
Ref: Product.id < ProductInventory.id
Ref: Product.categoryId > ProductCategory.id
Ref: Product.discountId > Discount.id
Ref: Order.id < OrderItem.orderId
Ref: Order.id < OrderDetails.orderId
Ref: Order.id < PaymentDetails.orderId
Ref: Order.id < Shipment.orderId