How To Create Database of Restorent Using Sql , MYSql

How To Create Database of Restorent Using Sql , MYSql

create table Restorent
(
id int primary key identity,
name varchar(100),
logourl varchar(200),
ownername varchar(100),
registrationno varchar(25),
tinno varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)

create table Roles
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)

create table Users
(
id int primary key identity,
name varchar(100),
imageurl varchar(200),
uid varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create UsersInRoles
(
id int primary key identity,
userid int foreign key refernces Users(id),
roleid int foreign key refernces Roles(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)


Create table Category
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
create table MenuType
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create table MenuItem
(
id int primary key identity,
name varchar(100),
description,
icon varchar(200),
price decimal(18,2),
Categoryid int int foreign key refernces Category(id),
Menutypeid int int foreign key refernces MenuType(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table sectiontype
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table sectionproperty
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create table section
(
id int primary key identity,
name varchar(100),
sectionpropertyid int int foreign key refernces sectionproperty(id),
sectiontypeid int int foreign key refernces sectiontype(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
Create table diningtablestatus
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table diningtable
(
id int primary key identity,
charcount int,
sectionid int int foreign key refernces section(id),
diningtablestatusid int foreign key refernces diningtablestatus(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table ordertype
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)



create table order
(
id int primary key identity,
ordertypeid int foreign key refernces ordertype(id),
orderdate datetime
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table orderdetails
(
id int primary key identity,
itemid int foreign  key references MenuItem(id),
Quantity int,
Amount decimal(18,2),
orderid int foreign key references order(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table diningtabletrack
(
id int primary key identity,
orderid int foreign key refernces order(id),
diningtableid int foreign key refernces  diningtable(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table Bills
(
id int primary key identity,
orderid int foreign key references order(id),
billdate datetime,
Amount decimal(18,2),
Discount decimal(18,2),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table Customer
(
id int primary key identity,
name varchar(100),
imageurl varchar(200),
uid varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table reservation
(
id int primary key identity,
reservationdate datetime,
customerid int foreign key references Customer(id),
diningtableid int foreign key references diningtable(id) 
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

--Possible values of this parameter:
--CC – for credit card payment mode
--DC – for debit card payment mode
--NB – for net banking payment mode
--Telco – for Operator Billing
--PPI – For Paytm Cash

Create table Paymenttype
(
id int primary key identity,
code varchar(10),
Description varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table cardtype
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
--SEAMLESS
--DEFAULT
--SUBSCRIBE
--RENEW_SUBSCRIPTION
Create table requesttype
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
--TXN_SUCCESS
--TXN_FAILURE
--TXN_PENDING
create table paytmstatus
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)