통합db

테이블 (31)

accounting

  • accounting_id — INT — 기본 키 — NOT NULL — PK
  • product_sales — DECIMAL(15, 2) — NOT NULL
  • beginning_inventory — DECIMAL(15, 2) — NOT NULL
  • ending_inventory — DECIMAL(15, 2) — NOT NULL
  • cost_of_goods_sold — DECIMAL(15, 2)
  • salary — DECIMAL(15, 2) — NOT NULL
  • transportation_cost — DECIMAL(15, 2) — NOT NULL
  • office_supplies_cost — DECIMAL(15, 2) — NOT NULL
  • rent_expense — DECIMAL(15, 2) — NOT NULL
  • net_profit — DECIMAL(15, 2)
  • notes — TEXT
  • date — DATE — 외래 키 → calendar.date — NOT NULL — FK
  • user_id — INT — 외래 키 → users.user_id — NOT NULL — FK

admin

  • username — varchar(30) — 기본 키 — NOT NULL — PK
  • password — varchar(255) — NOT NULL
  • email — varchar(50) — NOT NULL
  • tel — char(11) — NOT NULL
  • status — enum('ACTIVE','SUSPENDED','DELETED')
  • role — varchar(10) — NOT NULL

beverages

  • beverage_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • volume — DECIMAL(10, 2) — NOT NULL
  • price — DECIMAL(10, 2) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK

calendar

  • date — DATE — 기본 키 — NOT NULL — PK
  • sales_amount — DECIMAL(15, 2)

cart

  • cart_id — INT — 기본 키 — NOT NULL — PK
  • session_id — VARCHAR(255) — NOT NULL
  • quantity — INT — NOT NULL
  • added_at — TIMESTAMP
  • menu_id — INT — 외래 키 → menu.menu_id — NOT NULL — FK

categories

  • category_id — INT — 기본 키 — NOT NULL — PK
  • category_name — VARCHAR(50) — NOT NULL

cocktail_ingredients

  • ingredient_id — INT — 기본 키 — NOT NULL — PK
  • amount — DECIMAL(5, 2) — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • cocktail_id — INT — 외래 키 → cocktails.cocktail_id — NOT NULL — FK
  • spirit_id — INT — 외래 키 → spirits.spirit_id — NOT NULL — FK
  • beverage_id — INT — 외래 키 → beverages.beverage_id — NOT NULL — FK
  • fruit_id — INT — 외래 키 → fruits.fruit_id — NOT NULL — FK
  • mixer_id — INT — 외래 키 → mixers.mixer_id — NOT NULL — FK
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK

cocktails

  • cocktail_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • alcohol_content — DECIMAL(5, 2) — NOT NULL
  • price — DECIMAL(10, 2)
  • recipe — TEXT — NOT NULL
  • category — VARCHAR(50)

daily_statistics

  • date — date — 기본 키 — NOT NULL — PK
  • total_visitors — int
  • new_users — int

employee_management

  • employee_id — INT — 기본 키 — NOT NULL — PK
  • employee_name — VARCHAR(100) — NOT NULL
  • hourly_wage — DECIMAL(10, 2) — NOT NULL
  • total_hours_worked — DECIMAL(5, 2)
  • last_reset_date — DATE
  • user_id — INT — 외래 키 → users.user_id — NOT NULL — FK

fruits

  • fruit_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • quantity — INT — NOT NULL
  • price — DECIMAL(10, 2) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK

ingredients

  • id — int — 기본 키 — NOT NULL — PK
  • name — varchar(255) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK
  • product_id — int — 외래 키 → products.id — NOT NULL — FK

inventory

  • inventory_id — INT — 기본 키 — NOT NULL — PK
  • quantity — DECIMAL(10, 2) — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • reorder_level — DECIMAL(10, 2) — NOT NULL
  • last_updated — TIMESTAMP
  • ingredient_id — INT — 외래 키 → cocktail_ingredients.ingredient_id — NOT NULL — FK

login_history

  • id — int — 기본 키 — NOT NULL — PK
  • login_date — datetime
  • user_id — INT — 외래 키 → users.user_id — NOT NULL — FK

menu

  • menu_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(50) — 기본 키 — NOT NULL — PK
  • price — DECIMAL(10, 2) — NOT NULL
  • description — TEXT
  • created_at — TIMESTAMP
  • cocktail_id — INT — 외래 키 → cocktails.cocktail_id — NOT NULL — FK
  • allergy_ingredients — VARCHAR(255)
  • status — ENUM('판매중', '품절') — NOT NULL
  • photo_path — VARCHAR(255)

menu_list

  • menu_id — INT — 외래 키 → menu.menu_id — NOT NULL — FK
  • menu_name — VARCHAR(50) — 외래 키 → menu.name — NOT NULL — FK
  • registration_date — DATE — NOT NULL
  • status — ENUM('판매중', '품절') — NOT NULL

mixers

  • mixer_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • volume — DECIMAL(10, 2) — NOT NULL
  • price — DECIMAL(10, 2) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK

my_tables

  • table_number — INT — 기본 키 — NOT NULL — PK

notice

  • notice_num — int — 기본 키 — NOT NULL — PK
  • notice_title — varchar(300) — NOT NULL
  • notice_contents — text — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime
  • deadline — date
  • status — enum('ACTIVE','EXPIRED')

order_details

  • detail_id — INT — 기본 키 — NOT NULL — PK
  • menu_id — INT — 외래 키 → menu.menu_id — NOT NULL — FK
  • order_id — INT — 외래 키 → orders.order_id — NOT NULL — FK
  • quantity — INT — NOT NULL
  • price — DECIMAL(10,2) — NOT NULL

orders

  • order_id — INT — 기본 키 — NOT NULL — PK
  • session_id — VARCHAR(255) — NOT NULL
  • total_price — DECIMAL(10,2) — NOT NULL
  • order_date — TIMESTAMP
  • status — ENUM('pending', 'completed', 'canceled') — NOT NULL
  • table_number — INT — 외래 키 → my_tables.table_number — NOT NULL — FK

products

  • id — int — 기본 키 — NOT NULL — PK
  • name — varchar(255) — NOT NULL
  • productType — varchar(50)
  • price — decimal(10,2) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — FK
  • spirit_id — INT — 외래 키 → spirits.spirit_id — FK
  • beverage_id — INT — 외래 키 → beverages.beverage_id — FK
  • fruit_id — INT — 외래 키 → fruits.fruit_id — FK
  • mixer_id — INT — 외래 키 → mixers.mixer_id — FK

purchase_requests

  • request_id — INT — 기본 키 — NOT NULL — PK
  • category — VARCHAR(50) — NOT NULL
  • quantity — INT — NOT NULL
  • request_date — DATE — NOT NULL
  • due_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • notes — TEXT
  • supplier_id — INT — 외래 키 → suppliers.supplier_id — NOT NULL — FK
  • ingredient_id — INT — 외래 키 → cocktail_ingredients.ingredient_id — NOT NULL — FK

recipes

  • recipe_id — INT — 기본 키 — NOT NULL — PK
  • amount — DECIMAL(5, 2) — NOT NULL
  • unit — VARCHAR(20) — NOT NULL
  • cocktail_id — INT — 외래 키 → cocktails.cocktail_id — NOT NULL — FK
  • ingredient_id — INT — 외래 키 → cocktail_ingredients.ingredient_id — NOT NULL — FK

recommend

  • rec_id — INT — 기본 키 — NOT NULL — PK
  • api_source — VARCHAR(255) — NOT NULL
  • menu_id — INT — 외래 키 → menu.menu_id — NOT NULL — FK

salary_records

  • id — INT — 기본 키 — NOT NULL — PK
  • amount — DECIMAL(10, 2) — NOT NULL
  • payment_date — DATE — NOT NULL
  • employee_id — INT — 외래 키 → employee_management.employee_id — NOT NULL — FK

sales

  • sale_id — INT — 기본 키 — NOT NULL — PK
  • sale_date — DATE — NOT NULL
  • quantity — INT — NOT NULL
  • total_amount — DECIMAL(15, 2) — NOT NULL
  • user_id — INT — 외래 키 → users.user_id — NOT NULL — FK
  • menu_id — INT — 외래 키 → menu.menu_id — NOT NULL — FK

spirits

  • spirit_id — INT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • volume — DECIMAL(10, 2) — NOT NULL
  • price — DECIMAL(10, 2) — NOT NULL
  • category_id — INT — 외래 키 → categories.category_id — NOT NULL — FK

supplier_ingredients

  • supplier_ingredient_id — INT — 기본 키 — NOT NULL — PK
  • supplier_id — INT — 외래 키 → suppliers.supplier_id — NOT NULL — FK
  • ingredient_id — INT — 외래 키 → cocktail_ingredients.ingredient_id — NOT NULL — FK

suppliers

  • supplier_id — INT — 기본 키 — NOT NULL — PK
  • business_number — VARCHAR(10) — NOT NULL — UNIQUE
  • name — VARCHAR(100) — NOT NULL
  • representative_name — VARCHAR(50)
  • phone_number — VARCHAR(15) — NOT NULL
  • notes — TEXT

users

  • user_id — INT — 기본 키 — NOT NULL — PK
  • username — VARCHAR(50) — NOT NULL — UNIQUE
  • name — VARCHAR(50) — NOT NULL
  • company_name — VARCHAR(100)
  • password — VARCHAR(255) — NOT NULL
  • phone_number — VARCHAR(15) — NOT NULL
  • secondary_phone_number — VARCHAR(15)
  • business_number — VARCHAR(20) — NOT NULL — UNIQUE
  • email — VARCHAR(100) — NOT NULL — UNIQUE
  • account_non_locked — BOOLEAN
  • registration_date — TIMESTAMP