You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
77 lines
2.5 KiB
77 lines
2.5 KiB
create database management;
|
|
use management;
|
|
CREATE TABLE demander (
|
|
ID int NOT NULL auto_increment,
|
|
name varchar(255) UNIQUE,
|
|
address varchar(255),
|
|
telephone varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
CREATE TABLE materials (
|
|
ID int NOT NULL auto_increment,
|
|
name varchar(255) NOT NULL UNIQUE,
|
|
type varchar(255) NOT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
CREATE TABLE order_form (
|
|
demander_ID int NOT NULL,
|
|
ID int NOT NULL auto_increment,
|
|
type varchar(255) NOT NULL,
|
|
emergency varchar(255) DEFAULT NULL,
|
|
remark varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (ID),
|
|
FOREIGN KEY (demander_ID) REFERENCES demander (ID) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE provider (
|
|
ID int NOT NULL auto_increment,
|
|
name varchar(255) UNIQUE,
|
|
address varchar(255),
|
|
telephone varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
CREATE TABLE warehouse (
|
|
ID int NOT NULL auto_increment,
|
|
address varchar(255) NOT NULL,
|
|
PRIMARY KEY (ID)
|
|
);
|
|
CREATE TABLE provide (
|
|
provider_ID int NOT NULL,
|
|
materials_ID int NOT NULL,
|
|
time datetime NOT NULL,
|
|
quantity decimal(8,0) DEFAULT NULL CHECK (quantity >= 0),
|
|
verification BOOLEAN,
|
|
PRIMARY KEY (provider_ID,materials_ID),
|
|
KEY materials_ID (materials_ID),
|
|
FOREIGN KEY (provider_ID) REFERENCES provider (ID) ON DELETE CASCADE,
|
|
FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE send (
|
|
warehouse_ID int NOT NULL,
|
|
materials_ID int NOT NULL,
|
|
demander_ID int NOT NULL,
|
|
time datetime DEFAULT NULL,
|
|
quantity decimal(8,0) DEFAULT NULL CHECK (quantity >= 0),
|
|
verification BOOLEAN,
|
|
PRIMARY KEY (warehouse_ID,materials_ID,demander_ID),
|
|
KEY materials_ID (materials_ID),
|
|
KEY demander_ID (demander_ID),
|
|
CONSTRAINT send_ibfk_1 FOREIGN KEY (warehouse_ID) REFERENCES warehouse (ID) ON DELETE CASCADE,
|
|
CONSTRAINT send_ibfk_2 FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE,
|
|
CONSTRAINT send_ibfk_3 FOREIGN KEY (demander_ID) REFERENCES demander (ID) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE stockpile (
|
|
warehouse_ID int NOT NULL,
|
|
materials_ID int NOT NULL,
|
|
quantity decimal(8,0) DEFAULT NULL,
|
|
PRIMARY KEY (warehouse_ID,materials_ID),
|
|
KEY materials_ID (materials_ID),
|
|
CONSTRAINT stockpile_ibfk_1 FOREIGN KEY (warehouse_ID) REFERENCES warehouse (ID) ON DELETE CASCADE,
|
|
CONSTRAINT stockpile_ibfk_2 FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE,
|
|
CONSTRAINT stockpile_chk_1 CHECK ((quantity >= 0))
|
|
);
|
|
CREATE TABLE users(
|
|
ID INT,
|
|
password varchar(255),
|
|
type varchar(255) check (type in ('demander','provider','admin')),
|
|
PRIMARY KEY (id,type)
|
|
); |