aboutsummaryrefslogtreecommitdiff
path: root/backend/migrations/2020-12-13-140215_all_tables/up.sql
blob: 527b92601984d68246db538ab22ff6e158c302a7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Your SQL goes here

create table customer (
    id integer primary key auto_increment,
    username varchar(255) not null unique,
    password varchar(255) not null,
    phone_number varchar(10) not null,
    email_id varchar(255) not null,
    address text(500)
);

create table product (
    id integer primary key auto_increment,
    name varchar(255) not null,
    kind varchar(255),
    price float not null,
    description varchar(255)
);

create table cart_items (
    cart_id integer,
    product_id integer,
    constraint cart_items_pk primary key (cart_id, product_id),
    foreign key (cart_id) references customer(id),
    foreign key (product_id) references product(id)
);

create table rating (
    id integer primary key auto_increment,
    comment_text text(500),
    comment_date date default curdate(),
    product_id integer,
    customer_id integer,

    stars integer check (stars >= 0 AND stars <= 5),
    foreign key (customer_id) references customer(id),
    foreign key (product_id) references product(id)
);

create table transaction (
    id integer primary key auto_increment,
    payment_type varchar(255) not null,
    amount float not null,
    customer_id integer,

    foreign key (customer_id) references customer(id)
);