Datenbankmodell

---

Preview

---

DBML

Project BakeryERP {
  database_type: "PostgreSQL"
}

/* ===== MASTER DATA TABLES ===== */

Table branches {
  id int [pk, increment]
  name varchar
  address_line varchar
  postal_code varchar(20)
  city varchar
  country varchar(2)
  phone varchar(30)
  email varchar
  opening_note text
  created_at timestamp
  updated_at timestamp
}

Table branch_opening_hours {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  weekday int [note: "0=Sunday, 6=Saturday"]
  opens time
  closes time
}

Table branch_order_window {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  weekday int
  order_start time
  order_end time
}

/* --- Users & Employees --- */

Table roles {
  id int [pk, increment]
  name varchar
  description text
}

Table users {
  id int [pk, increment]
  username varchar
  password_hash varchar
  role_id int [ref: > roles.id]
  employee_id int [ref: > employees.id, note: "nullable for non‑employee accounts"]
  is_active boolean [default: true]
  created_at timestamp
  updated_at timestamp
}

Table employees {
  id int [pk, increment]
  first_name varchar
  last_name varchar
  email varchar
  phone varchar
  hire_date date
  termination_date date
  hourly_wage numeric(10,2)
  monthly_salary numeric(10,2)
  created_at timestamp
  updated_at timestamp
}

Table employee_time_entries {
  id int [pk, increment]
  employee_id int [ref: > employees.id]
  branch_id int [ref: > branches.id]
  clock_in timestamp
  clock_out timestamp
  break_minutes int
}

/* --- Finance & Accounts --- */

Table accounts {
  id int [pk, increment]
  name varchar
  account_number varchar
  iban varchar
  bic varchar
  description text
}

Table payment_methods {
  id int [pk, increment]
  name varchar
  external_code varchar
  is_cash boolean
}

/* --- Products, Ingredients, Recipes --- */

Table tax_codes {
  id int [pk, increment]
  name varchar
  rate numeric(5,2)
  valid_from date
  valid_to date
}

Table allergens {
  id int [pk, increment]
  code varchar(5)
  name varchar
  description text
}

Table ingredients {
  id int [pk, increment]
  name varchar
  unit varchar(10) [note: "e.g. kg, l, piece"]
  purchase_price numeric(12,4)
  tax_code_id int [ref: > tax_codes.id]
  is_active boolean [default: true]
  created_at timestamp
  updated_at timestamp
}

Table ingredient_allergens {
  ingredient_id int [pk, ref: > ingredients.id]
  allergen_id int [pk, ref: > allergens.id]
}

Table suppliers {
  id int [pk, increment]
  name varchar
  contact_name varchar
  email varchar
  phone varchar
  address_line varchar
  postal_code varchar
  city varchar
  country varchar(2)
  created_at timestamp
  updated_at timestamp
}

Table supplier_products {
  id int [pk, increment]
  supplier_id int [ref: > suppliers.id]
  ingredient_id int [ref: > ingredients.id]
  supplier_sku varchar
  purchase_price numeric(12,4)
  min_order_qty numeric(12,3)
  lead_time_days int
}

Table recipes {
  id int [pk, increment]
  name varchar
  description text
  created_at timestamp
  updated_at timestamp
}

Table recipe_items {
  id int [pk, increment]
  recipe_id int [ref: > recipes.id]
  ingredient_id int [ref: > ingredients.id]
  quantity numeric(14,4)
  unit varchar(10)
}

Table products {
  id int [pk, increment]
  name varchar
  sku varchar
  recipe_id int [ref: > recipes.id]
  sales_price numeric(12,2)
  tax_code_id int [ref: > tax_codes.id]
  is_active boolean [default: true]
  created_at timestamp
  updated_at timestamp
}

Table product_allergens {
  product_id int [pk, ref: > products.id]
  allergen_id int [pk, ref: > allergens.id]
}

/* --- Inventory & Warehouses --- */

Table warehouses {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  name varchar
  description text
}

Table inventory_locations {
  id int [pk, increment]
  warehouse_id int [ref: > warehouses.id]
  code varchar
  description text
}

Enum inventory_movement_type {
  IN
  OUT
  TRANSFER
  CORRECTION
  PRODUCED
  CONSUMED
}

Table inventory_movements {
  id int [pk, increment]
  movement_type inventory_movement_type
  ingredient_id int [ref: > ingredients.id]
  qty numeric(14,4)
  unit varchar(10)
  from_location_id int [ref: > inventory_locations.id]
  to_location_id int [ref: > inventory_locations.id]
  reference_type varchar(30)
  reference_id int
  created_at timestamp
  user_id int [ref: > users.id]
}

/* ===== TRANSACTIONAL DATA ===== */

/* --- Customer Orders & Sales --- */

Table customers {
  id int [pk, increment]
  type varchar(10) [note: "B2B or B2C"]
  company_name varchar
  first_name varchar
  last_name varchar
  email varchar
  phone varchar
  address_line varchar
  postal_code varchar
  city varchar
  country varchar(2)
  created_at timestamp
  updated_at timestamp
}

Enum order_status {
  NEW
  CONFIRMED
  IN_PRODUCTION
  READY
  DELIVERED
  CANCELLED
}

Table customer_orders {
  id int [pk, increment]
  order_number varchar unique
  customer_id int [ref: > customers.id]
  customer_name varchar  //in case customer doesn't have DB entry
  branch_id int [ref: > branches.id]
  order_datetime timestamp
  desired_datetime timestamp
  serial text  //RFC 5545 string
  serial_end timestamp
  status order_status
  total_amount numeric(12,2)
  payment_status varchar(20)
  comment text
}

Table customer_order_items {
  id int [pk, increment]
  order_id int [ref: > customer_orders.id]
  product_id int [ref: > products.id]
  quantity int
  unit_price numeric(12,2)  //might be different from the regular product price, esp. for B2B
  tax_code_id int [ref: > tax_codes.id]
}

/* Point of Sale (Cash Register) */

Table sales_receipts {
  id int [pk, increment]
  receipt_number varchar
  branch_id int [ref: > branches.id]
  pos_terminal_id int [ref: > pos_terminals.id]
  sale_datetime timestamp
  customer_id int [ref: > customers.id]
  total_amount numeric(12,2)
  payment_method_id int [ref: > payment_methods.id]
  payment_reference varchar
}

Table sales_receipt_items {
  id int [pk, increment]
  receipt_id int [ref: > sales_receipts.id]
  product_id int [ref: > products.id]
  qty numeric(12,3)
  unit_price numeric(12,2)
  tax_code_id int [ref: > tax_codes.id]
}

/* Feedback & Complaints */

Table customer_feedback {
  id int [pk, increment]
  customer_id int [ref: > customers.id]
  branch_id int [ref: > branches.id]
  order_id int [ref: > customer_orders.id]
  rating int
  comment text
  created_at timestamp
}

Table complaints {
  id int [pk, increment]
  customer_id int [ref: > customers.id]
  order_id int [ref: > customer_orders.id]
  product_id int [ref: > products.id]
  description text
  resolved boolean [default: false]
  created_at timestamp
  resolved_at timestamp
}

/* --- Production --- */

Enum production_status {
  PLANNED
  IN_PROGRESS
  FINISHED
  CONFIRMED
}

Table production_plans {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  planned_date date
  status production_status
  created_at timestamp
  updated_at timestamp
}

Table production_plan_items {
  id int [pk, increment]
  plan_id int [ref: > production_plans.id]
  product_id int [ref: > products.id]
  planned_qty numeric(12,3)
  produced_qty numeric(12,3)
}

Table production_feedback {
  id int [pk, increment]
  plan_item_id int [ref: > production_plan_items.id]
  user_id int [ref: > users.id]
  produced_qty numeric(12,3)
  timestamp timestamp
}

/* --- Shifts & Scheduling --- */

Table shifts {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  name varchar
  start_time time
  end_time time
}

Table shift_schedule {
  id int [pk, increment]
  shift_id int [ref: > shifts.id]
  employee_id int [ref: > employees.id]
  schedule_date date
  assigned_hours numeric(5,2)
}

/* --- Purchasing & AP --- */

Enum supplier_order_status {
  OPEN
  ORDERED
  RECEIVED
  CLOSED
}

Table supplier_orders {
  id int [pk, increment]
  order_number varchar
  supplier_id int [ref: > suppliers.id]
  branch_id int [ref: > branches.id]
  order_date date
  status supplier_order_status
  expected_date date
  total_amount numeric(12,2)
}

Table supplier_order_items {
  id int [pk, increment]
  order_id int [ref: > supplier_orders.id]
  ingredient_id int [ref: > ingredients.id]
  qty numeric(12,3)
  unit_price numeric(12,4)
  received_amount numeric(12,3)
  received boolean [default:false]
  received_at timestamp [note: "Wenn NOT NULL, gilt als empfangen (received = true)"]
  tax_code_id int [ref: > tax_codes.id]
}


Table goods_receipts {
  id int [pk, increment]
  receipt_number varchar
  supplier_order_id int [ref: > supplier_orders.id]
  receipt_date date
  created_at timestamp
}

Table goods_receipt_items {
  id int [pk, increment]
  receipt_id int [ref: > goods_receipts.id]
  ingredient_id int [ref: > ingredients.id]
  qty numeric(12,3)
  unit_price numeric(12,4)
  tax_code_id int [ref: > tax_codes.id]
}

Table supplier_invoices {
  id int [pk, increment]
  invoice_number varchar
  supplier_id int [ref: > suppliers.id]
  supplier_order_id int [ref: > supplier_orders.id]
  invoice_date date
  due_date date
  total_amount numeric(12,2)
  payment_status varchar(20)
}

Table supplier_invoice_items {
  id int [pk, increment]
  invoice_id int [ref: > supplier_invoices.id]
  ingredient_id int [ref: > ingredients.id]
  qty numeric(12,3)
  unit_price numeric(12,4)
  tax_code_id int [ref: > tax_codes.id]
}

/* --- Payments --- */

Table payment_transactions {
  id int [pk, increment]
  account_id int [ref: > accounts.id]
  payment_method_id int [ref: > payment_methods.id]
  reference_type varchar(30)
  reference_id int
  amount numeric(14,2)
  currency char(3)
  direction varchar(10) [note: "IN or OUT"]
  transaction_date timestamp
  created_at timestamp
}

/* --- Machines & POS Terminals --- */

Table machines {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  name varchar
  serial_number varchar
  purchase_date date
  last_maintenance date
}

Table pos_terminals {
  id int [pk, increment]
  branch_id int [ref: > branches.id]
  terminal_code varchar
  description text
  is_active boolean [default: true]
}

/* --- Refunds & Returns --- */

Table refund_reasons {
  id int [pk, increment]
  code varchar(20)
  description text
}

Table refunds {
  id int [pk, increment]
  receipt_id int [ref: > sales_receipts.id, note: "nullable if refund is based on customer order"]
  order_id int [ref: > customer_orders.id, note: "nullable if refund is based on POS"]
  refund_datetime timestamp
  total_refund_amount numeric(12,2)
  payment_method_id int [ref: > payment_methods.id]
  account_id int [ref: > accounts.id, note: "Bank/cash account used for refund"]
  reason_id int [ref: > refund_reasons.id]
  created_by int [ref: > users.id]
  note text
  created_at timestamp
}

Table refund_items {
  id int [pk, increment]
  refund_id int [ref: > refunds.id]
  product_id int [ref: > products.id]
  qty numeric(12,3)
  unit_price numeric(12,2)
  tax_code_id int [ref: > tax_codes.id]
}