Logical to Physical Data Model Mapping

LOGICAL                     PHYSICAL
Entity                  ->  table (plural snake_case)
Business identifier     ->  UNIQUE column + surrogate uuid PK
1:N relationship        ->  FK on the many side + explicit ON DELETE
Associative entity      ->  table + composite UNIQUE across the pair
Reference set           ->  seeded lookup table (or CHECK for tiny sets)
Derived attribute       ->  view / computed on read, NOT a column
Rule                    ->  CHECK constraint
Who may read / write    ->  RLS enabled + one policy per operation

-- Entity: LOAN  (logical model v1, approved)
create table public.loans (
  id           uuid primary key default gen_random_uuid(),
  loan_number  text not null unique,                 -- business identifier
  member_id    uuid not null references public.members (id) on delete restrict,
  tool_id      uuid not null references public.tools (id)   on delete restrict,
  borrowed_on  date not null,
  due_on       date not null,
  constraint due_after_borrowed check (due_on >= borrowed_on)   -- rule
);
create index loans_member_id_idx on public.loans (member_id);  -- FKs are not auto-indexed
alter table public.loans enable row level security;
-- status is NOT a column: it is the view loan_status (derived)