Online Shopping System - HUST Database Course Project//
Operator Roles: Administrator
, Merchant
& Customer
.
Users Table:
UserID
int auto-inc primary-key
username
string
password
string
role
string: admin
/merchant
/customer
.
User Information Table:
UInfoID
int auto-inc primary-key
UID
int foreign-key(User Table)
Name
string
Phone
string
Address
string
Shop Information Table:
ShopID
int auto-inc primary-key
BelongsTo
int foreign-key(User Table)
ShopName
string
Stock Information Table:
StockId
int auto-inc primary-key
SoldAt
int foreign-key(ShopInformationTable)
Name
string
Type
string
Desc
string
Price
float
Picture
string, base64 encoded picture
Promotion
float
StockCount
int
Enabled
boolean
Order Information Table
OrderId
int auto-inc primary-key
FromCustomer
int foreign-key(UserInformationTable)
OrderedStock
int foreign-key(StockInformationTable)
Amount
int
InstantPrice
float
PriceSum
float
PaymentDetail
string
DeliveryDetail
string
Status
string: issued
, paid
, delivered
, finished
, cancelled
.
create table users (
uid int not null auto_increment,
username text not null,
password text not null,
role text not null,
primary key (uid)
);
create table user_info (
uinfoid int not null auto_increment,
uid int not null,
name text not null,
phone text not null,
address text not null,
primary key (uinfoid),
foreign key (uid) references users (uid)
);
create table shop_info (
shopid int not null auto_increment,
belongs int not null,
name text not null,
primary key (shopid),
foreign key (belongs) references users (uid)
);
create table stock_info (
stockid int not null auto_increment,
sold_at int not null,
name text not null,
type text not null,
description text not null,
price double not null,
picture text not null,
promotion double not null,
stock_count int not null,
enabled boolean not null,
primary key (stockid),
foreign key (sold_at) references shop_info (shopid)
);
create table order_info (
orderid int not null auto_increment,
cust int not null,
stock int not null,
shop int not null,
amount int not null,
inst_price double not null,
price_sum double not null,
payment_detail text not null,
delivery_detail text not null,
stat text not null,
primary key (orderid),
foreign key (cust) references users (uid),
foreign key (shop) references shop_info (shopid),
foreign key (stock) references stock_info (stockid)
);