Introduction
In creating a robust and efficient database for an online food ordering service, defining the structure of tables is a crucial step. Data Definition Language (DDL) is used for this purpose, as it provides the necessary SQL commands to create, modify, and delete database structures. This article will provide DDL statements for each essential table in the database of an online food ordering service, covering users, restaurants, menus, orders, and more.
DDL for Online Food Ordering Service Database
Each DDL statement below is designed to create a specific table in the database, taking into account the necessary columns and their data types.
1. User Table
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50),
Password VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(30),
DeliveryAddress VARCHAR(255)
);
2. Restaurant Table
CREATE TABLE Restaurants (
RestaurantID INT PRIMARY KEY,
RestaurantName VARCHAR(100),
CuisineType VARCHAR(50),
Address VARCHAR(255),
PhoneNumber VARCHAR(30),
Email VARCHAR(100)
);
3. Menu Table
CREATE TABLE Menu (
MenuItemID INT PRIMARY KEY,
RestaurantID INT,
ItemName VARCHAR(100),
Description TEXT,
Price DECIMAL(10, 2),
Category VARCHAR(50),
FOREIGN KEY (RestaurantID) REFERENCES Restaurants(RestaurantID)
);
4. Order Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
UserID INT,
OrderDate TIMESTAMP,
DeliveryAddress VARCHAR(255),
TotalAmount DECIMAL(10, 2),
OrderStatus VARCHAR(50),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
5. OrderDetails Table
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY,
OrderID INT,
MenuItemID INT,
Quantity INT,
Price DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (MenuItemID) REFERENCES Menu(MenuItemID)
);
6. Payment Table
CREATE TABLE Payments (
PaymentID INT PRIMARY KEY,
OrderID INT,
PaymentDate TIMESTAMP,
Amount DECIMAL(10, 2),
PaymentMethod VARCHAR(50),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
7. Reviews Table
CREATE TABLE Reviews (
ReviewID INT PRIMARY KEY,
UserID INT,
RestaurantID INT,
Rating INT,
Comment TEXT,
ReviewDate TIMESTAMP,
FOREIGN KEY (UserID) REFERENCES Users(UserID),
FOREIGN KEY (RestaurantID) REFERENCES Restaurants(RestaurantID)
);
8. Delivery Staff Table
CREATE TABLE DeliveryStaff (
StaffID INT PRIMARY KEY,
Name VARCHAR(100),
ContactNumber VARCHAR(30),
DeliveryArea VARCHAR(100)
);
9. Delivery Table
CREATE TABLE Deliveries (
DeliveryID INT PRIMARY KEY,
OrderID INT,
StaffID INT,
DeliveryStatus VARCHAR(50),
DeliveryTime TIMESTAMP,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (StaffID) REFERENCES DeliveryStaff(StaffID)
);
Conclusion
The DDL statements provided above establish the foundation for a comprehensive database tailored to an online food ordering service. These tables collectively cover all aspects of the service, from user information to order processing and delivery. By using these DDL statements, database administrators and developers can create a robust database structure, paving the way for efficient data management and a seamless user experience in the online food ordering platform.
This article presents detailed DDL statements for creating each essential table in an online food ordering service database. These statements are designed to ensure a well-structured, efficient, and comprehensive database, catering to all the critical aspects of the service.
Top comments (0)