-
Notifications
You must be signed in to change notification settings - Fork 0
/
Meato_DB_HLDesignV1.0.sql
241 lines (190 loc) · 6.71 KB
/
Meato_DB_HLDesignV1.0.sql
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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
--AD_USER_TB
---------
Drop table AD_USER_TB;
Create table AD_USER_TB {
ID NUMBER(10) NOT NULL , --101
FIRST_NAME VARCHAR(100) NOT NULL, -- shlpa
LAST_NAME VARCHAR(100) , -- Salvi
EMAIL VARCHAR(100) NOT NULL,[email protected]
TELEPHONE VARCHAR(50) NOT NULL,--12345
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_User PRIMARY KEY (ID)
};
--AD_ADDRESS_TB
------------
Drop table AD_ADDRESS_TB;
Create table AD_ADDRESS_TB {
ID NUMBER(10) NOT NULL , --1
LINE1 VARCHAR(255) NOT NULL, -- A-2,405
LINE2 VARCHAR(255), --KM street
LANDMARK VARCHAR(255) NOT NULL, --Hotel
STATE NUMBER(10) NOT NULL, -- MH
CITY NUMBER(10) NOT NULL,-- MUM
COUNTRY NUMBER(10) NOT NULL,-- IN
PINCODE NUMBER(6) NOT NULL, --400
USER_ID NUMBER(10) NOT NULL ,
ADRESS_TYPE VARCHAR(25) NOT NULL, --HOME,WORK
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_Adress PRIMARY KEY (ID)
};
ALTER TABLE AD_ADDRESS_TB
ADD CONSTRAINT FK_UserAddress
FOREIGN KEY (ID) REFERENCES AD_USER_TB(ADDRESS_ID);
--AD_STATE_TB
------------
Drop table AD_STATE_TB;
Create table AD_STATE_TB {
SHORT_NAME VARCHAR(2) NOT NULL, -- MH
LONG_NAME VARCHAR(255) ,-- maharashtra
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_State PRIMARY KEY (SHORT_NAME)
};
ALTER TABLE AD_STATE_TB
ADD CONSTRAINT FK_State
FOREIGN KEY (SHORT_NAME) REFERENCES AD_ADDRESS_TB(STATE);
--AD_CITY_TB
Drop table AD_CITY_TB;
Create table AD_CITY_TB {
SHORT_NAME VARCHAR(2) NOT NULL,
LONG_NAME VARCHAR(255),
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_City PRIMARY KEY (SHORT_NAME)
};
ALTER TABLE AD_CITY_TB
ADD CONSTRAINT FK_State
FOREIGN KEY (SHORT_NAME) REFERENCES AD_ADDRESS_TB(CITY);
--AD_COUNTRY_TB
Drop table AD_COUNTRY_TB;
Create table AD_COUNTRY_TB {
SHORT_NAME VARCHAR(2) NOT NULL,
LONG_NAME VARCHAR(255),
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY (SHORT_NAME)
};
ALTER TABLE AD_COUNTRY_TB
ADD CONSTRAINT FK_Country
FOREIGN KEY (SHORT_NAME) REFERENCES AD_ADDRESS_TB(COUNTRY);
--AD_FOOD_UNIT_TB --SaleUnit
--------------
Drop table AD_FOOD_UNIT_TB;
Create table AD_FOOD_UNIT_TB {
SHORT_NAME VARCHAR(5) NOT NULL, -- PC,KG,GRM,DZ
LONG_NAME VARCHAR(255), --Piece,Kilogram,Gram,Dozen
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_FoodUnit PRIMARY KEY (SHORT_NAME)
};
-- AD_FOOD_CATEGORY_TB --Category
Drop table AD_FOOD_CATEGORY_TB;
Create table AD_FOOD_CATEGORY_TB {
SHORT_NAME VARCHAR(5) NOT NULL, --Mutton,Chicken,Marinade,Spice,Dessert,Sea Food, Spices
LONG_NAME VARCHAR(255), --Mutton,Chicken,Marinade,Spice,Dessert,Sea Food
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_FoodCategory PRIMARY KEY (SHORT_NAME)
};
-- AD_FOOD_SUB_CATEGORY_TB -- Sub Category
Drop table AD_FOOD_SUB_CATEGORY_TB;
Create table AD_FOOD_SUB_CATEGORY_TB {
SHORT_NAME VARCHAR(5) NOT NULL, --Promphet,prawns, halal, boiler
LONG_NAME VARCHAR(255), ----Promphet,prawns, halal, boiler
FOOD_CATEGORY VARCHAR(2) NOT NULL, ----Mutton,Chicken,Marinade,Spice,Dessert,Sea Food
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_FoodSubCategory PRIMARY KEY (SHORT_NAME)
};
ALTER TABLE AD_FOOD_SUB_CATEGORY_TB
ADD CONSTRAINT FK_SubCategory
FOREIGN KEY (FOOD_CATEGORY) REFERENCES AD_FOOD_CATEGORY_TB(SHORT_NAME);
---AD_ITEM_RATE_TB -- ITEM
Drop table AD_ITEM_RATE_TB;
Create table AD_ITEM_RATE_TB {
ID NUMBER (10) NOT NULL UNIQUE,
CATEGORY VARCHAR(5) NOT NULL,-- Mutton,Chicken,Marinade,Spice,Dessert,Sea Food ,Spices
SUB_CATEGORY VARCHAR(5) NOT NULL,-- Promphet, prawns
SALE_UNIT VARCHAR(5) NOT NULL,-- PC,KG,GRM,DZ
DESCRIPTION VARCHAR(255) NOT NULL,--
NAME VARCHAR(255) NOT NULL,-- --
RATE NUMBER (10,2),
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_Rate PRIMARY KEY (CATEGORY,SUB_CATEGORY,SALE_UNIT)
};
ALTER TABLE AD_ITEM_RATE_TB
ADD CONSTRAINT FK_RateCategory
FOREIGN KEY (CATEGORY) REFERENCES AD_FOOD_CATEGORY_TB(SHORT_NAME);
ALTER TABLE AD_ITEM_RATE_TB
ADD CONSTRAINT FK_RateSubCategory
FOREIGN KEY (SUB_CATEGORY) REFERENCES AD_FOOD_SUB_CATEGORY_TB(SHORT_NAME);
--AD_ORDER_STATUS_TB
Drop table AD_ORDER_STATUS_TB;
CREATE TABLE AD_ORDER_STATUS_TB {
SHORT_NAME VARCHAR (5) NOT NULL, -- Initiated,Confirmed, Cancelled,Failed,Delivered.
LONG_NAME VARCHAR (50) NOT NULL, -- Initiated,Confirmed, Cancelled,Failed,Delivered.
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
};
-- AD_SCHEMES_TB
Drop table AD_SCHEMES_TB;
CREATE TABLE AD_SCHEMES_TB {
--ID NUMBER(100) NOT NULL UNIQUE,
TYPE VARCHAR (50) NOT NULL,
DESCRIPTION VARCHAR (255) NOT NULL,
AMOUNT_GREATER_THAN NUMBER(7) NOT NULL,
PERCENTAGE NUMBER(3) NOT NULL,
CONSTRAINT PK_Schemes PRIMARY KEY (TYPE)
};
---TXN_CUSTOMER_ORDER_TB
Drop table TXN_CUSTOMER_ORDER_TB;
CREATE TABLE TXN_CUSTOMER_ORDER_TB {
ID NUMBER(100) NOT NULL,
-- ORDER_NUMBER NUMBER(100) NOT NULL,
ORDER_AMOUNT NUMBER(100) NOT NULL, -- totalAmount
ORDER_DATE TIMESTAMP NOT NULL,
USER_ID NUMBER(100)NOT NULL,
DELIVERY_ADDRESS_ID VARCHAR (50) NOT NULL,
ORDER_ITEM_COUNT NUMBER(100)NOT NULL, -- Total Items
SCHEME_TYPE NUMBER (3) NOT NULL,
DISCOUNTED_AMOUNT NUMBER(100) NOT NULL,
ORDER_STATUS VARCHAR (50) NOT NULL,
ESTIMATED_TIME TIMESTAMP NOT NULL,
DELEIVERED_TIME TIMESTAMP NOT NULL,
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_Order PRIMARY KEY (ID,USER_ID)
);
ALTER TABLE TXN_CUSTOMER_ORDER_TB
ADD CONSTRAINT FK_UserOrder
FOREIGN KEY (USER_ID) REFERENCES AD_USER_TB(ID);
ALTER TABLE TXN_CUSTOMER_ORDER_TB
ADD CONSTRAINT FK_UserOrderAddress
FOREIGN KEY (DELIVERY_ADDRESS_ID) REFERENCES AD_ADDRESS_TB(ID);
ALTER TABLE TXN_CUSTOMER_ORDER_TB
ADD CONSTRAINT FK_UserOrderStatus
FOREIGN KEY (ORDER_STATUS) REFERENCES AD_ORDER_STATUS_TB(SHORT_NAME);
ALTER TABLE TXN_CUSTOMER_ORDER_TB
ADD CONSTRAINT FK_UserOrderScheme
FOREIGN KEY (SCHEME_TYPE) REFERENCES AD_SCHEMES_TB(TYPE);
---TXN_ORDERS_MASTER_TB
Drop table TXN_ORDERS_MASTER_TB;
CREATE TABLE TXN_ORDERS_MASTER_TB {
ID NUMBER(100) NOT NULL UNIQUE,
ORDER_NUMBER NUMBER(100) NOT NULL,
ITEM_ID NUMBER(100) NOT NULL,
QUANTITY NUMBER(100) NOT NULL,
AMOUNT NUMBER(100) NOT NULL,
LAST_UPDATED TIMESTAMP NOT NULL,
LAST_UPDATED_BY VARCHAR(50) NOT NULL,
CONSTRAINT PK_Orderdetails PRIMARY KEY (ORDER_NUMBER,ITEM_ID)
);
ALTER TABLE TXN_ORDERS_MASTER_TB
ADD CONSTRAINT FK_UserOrderDetails
FOREIGN KEY (ORDER_NUMBER) REFERENCES TXN_CUSTOMER_ORDER_TB(ID);
ALTER TABLE TXN_ORDERS_MASTER_TB
ADD CONSTRAINT FK_UserOrderDetailsItem
FOREIGN KEY (ITEM_ID) REFERENCES AD_ITEM_RATE_TB(ID);