foodie.dbml

// Users (both customers and restaurants)
Table users {
  id int [pk, increment] // Primary Key
  username varchar
  email varchar
  password varchar
  user_type enum('customer', 'restaurant') // Differentiate between customer and restaurant
  created_at datetime
  updated_at datetime
}

// Customer-specific information
Table customers {
  id int [pk, ref: > users.id] // Foreign Key to users table
  name varchar
}

// Addresses for customers
Table addresses {
  id int [pk, increment] // Primary Key
  customer_id int [ref: > customers.id] // Foreign Key to customers table
  address_line varchar
  city varchar
  postal_code varchar
  country varchar
  latitude decimal
  longitude decimal
}

// Cuisines
Table cuisines {
  id int [pk, increment]
  name varchar // e.g., Chinese, Burger, Pizza
}

// Restaurants
Table restaurants {
  id int [pk, ref: > users.id] // Foreign Key to users table
  name varchar
  cuisine_id int [ref: > cuisines.id] // Foreign Key to cuisines table
  min_order float
  delivery_radius_km float // Defines the geo scope
  working_days varchar // e.g., "Mon-Fri"
  working_hours varchar // e.g., "10:00-22:00"
}

// Menu categories defined by restaurant owners
Table menu_categories {
  id int [pk, increment]
  restaurant_id int [ref: > restaurants.id]
  name varchar // e.g., Most Liked, Popular
}

// Products (dishes)
Table products {
  id int [pk, increment]
  category_id int [ref: > menu_categories.id]
  title varchar
  description text
  photo_url varchar
}

// Orders
Table orders {
  id int [pk, increment]
  customer_id int [ref: > customers.id]
  restaurant_id int [ref: > restaurants.id]
  status enum('preparing', 'on the way', 'delivered')
  total_price float
  order_time datetime
  delivery_address_id int [ref: > addresses.id] // Address for delivery
}

// Order Items
Table order_items {
  id int [pk, increment]
  order_id int [ref: > orders.id]
  product_id int [ref: > products.id]
  quantity int
  note text
}

// Ratings and Comments
Table ratings {
  id int [pk, increment]
  order_id int [ref: > orders.id]
  rating int
  comment text
}

// Restaurant Ratings
Table restaurant_ratings {
  id int [pk, increment]
  restaurant_id int [ref: > restaurants.id]
  customer_id int [ref: > customers.id]
  rating int
  comment text
}