통합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