BackendDM/db.sql at master · siddeshgawande/BackendDM · GitHub

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CREATE DATABASE mobiles_db;
USE mobiles_db;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS mobiles;
DROP TABLE IF EXISTS orders;


CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(20),
    lastName VARCHAR(20),
    email VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100),
    mobile VARCHAR(15),
    profileImage VARCHAR(100),
    createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE userAddress (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    city VARCHAR(100),
    state VARCHAR(100),
    zipcode VARCHAR(100),
    country VARCHAR(100),
    createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(30),
    description VARCHAR(1000),
    company VARCHAR(20),
    price DECIMAL(10,2),
    image VARCHAR(200),
    createdDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    productId INT,
    quantity INT,
    price DECIMAL(10, 2),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE userOrder (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    totalPrice DECIMAL(10, 2),
    orderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orderDetails (
    id INT PRIMARY KEY AUTO_INCREMENT,
    orderId INT,
    productId INT,
    price DECIMAL(10, 2),
    quantity INT,
    orderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);