Thiết kế schema cho khuyến mãi và mã giảm giá thế nào? Làm sao chặn một mã bị dùng quá số lần cho phép?

create table promotions (
  id            bigserial primary key,
  name          text not null,
  discount_type text not null check (discount_type in ('percent', 'fixed')),
  discount_value numeric(12,2) not null,
  max_discount  numeric(12,2),            -- cap for percent type
  min_order     numeric(12,2) default 0,
  starts_at     timestamptz not null,
  ends_at       timestamptz
);

create table coupons (
  id            bigserial primary key,
  promotion_id  bigint not null references promotions(id),
  code          text not null unique,
  max_redemptions int,                    -- null = unlimited
  per_user_limit  int default 1
);

create table coupon_redemptions (
  id        bigserial primary key,
  coupon_id bigint not null references coupons(id),
  user_id   bigint not null,
  order_id  bigint not null references orders(id),
  amount    numeric(12,2) not null,        -- snapshot of discount granted
  used_at   timestamptz not null default now()
);

update coupons set redeemed_count = redeemed_count + 1
 where id = $1 and (max_redemptions is null or redeemed_count < max_redemptions);