hoiks-server/create.sql at main · HOIKS/hoiks-server · 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CREATE TABLE User(

    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    password VARCHAR(100) NOT NULL,
    position VARCHAR(100) DEFAULT 'admin',
    phonenumber VARCHAR(100),

    PRIMARY KEY(id)
);

CREATE TABLE Store(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    masterUserId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(masterUserId) REFERENCES User(id)
);

CREATE TABLE Category(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    storeId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(storeId) REFERENCES Store(id)
);

CREATE TABLE SubCategory(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    categoryId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(categoryId) REFERENCES Category(id)
);

CREATE TABLE Menu(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    info VARCHAR(255),
    price INT DEFAULT 0,
    photoURL VARCHAR(255),

    PRIMARY KEY(id)
);

CREATE TABLE MenuSearch(

    id INT NOT NULL,
    subCategoryId INT NOT NULL,
    menuId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(subCategoryId) REFERENCES SubCategory(id),
    FOREIGN KEY(menuId) REFERENCES Menu(id)
);

CREATE TABLE MenuOption(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    menuId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(menuId) REFERENCES Menu(id)
);

CREATE TABLE OptionInfo(

    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    price INT DEFAULT 0,
    photoURL VARCHAR(255),
    optionId INT NOT NULL,

    PRIMARY KEY(id),
    FOREIGN KEY(optionId) REFERENCES MenuOption(id)
);