t7sXh1Sr

1create table products(
2    id int(20) not null auto_increment primary key,
3    product_name varchar(255) not null ,
4    price decimal(19.2) not null
5)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
6
7create table usr(
8    id int(20) not null auto_increment primary key,
9    name varchar (255) not null ,
10    username varchar (30) not null ,
11    password varchar (30) not null ,
12    account_id int not null ,
13    age int(11) not null ,
14    enabled bit(1)
15)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
16
17create table orders(
18    id int(20) not null auto_increment primary key ,
19    product_id int(20) not null ,
20    order_date date not null,
21)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
22
23create table accounts(
24    id int(20) not null auto_increment primary key ,
25    user_id int(20) not null ,
26    billing_address varchar (255),
27    amount decimal(19.2) not null,
28)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
29
30
31create table payments(
32    id int(20) not null auto_increment primary key ,
33    payment_date date not null ,
34    amount decimal (19.2) no null
35)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
36
37create table user_role(
38    user_id int(20) not null ,
39    roles varchar(255),
40    foreign key(user_id) references usr(id)
41)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
42
43ALTER TABLE IF EXISTS usr add constraint usr_account_fk foreign key (account_id) references accounts;
44
45ALTER TABLE IF EXISTS orders add constraint orders_products_fk foreign key (product_id) references products;
46
47ALTER TABLE IF EXISTS accounts add constraint accounts_usr_fk foreign key (user_id) references usr;
48
49ALTER TABLE IF EXISTS user_role add constraint user_role_user_fk foreign key (user_id) references usr;