数据建模草稿/报价/订单/发票

create table statement (
    stmt_id integer primary key,
    stmt_type char(1) not null check (stmt_type in (\'d\', \'q\', \'o\', \'i\')),
    stmt_date date not null default current_date,
    customer_id integer not null  -- references customer (customer_id)
);

create table statement_line_items (
    stmt_id integer not null references statement (stmt_id),
    line_item_number integer not null,
    -- other columns for line items
    primary key (stmt_id, line_item_number)
);

-- \"Supertype\". Comments appear above the column they apply to.
create table statement (
  -- Autoincrement or serial is ok here.
  stmt_id integer primary key,    
  stmt_type char(1) unique check (stmt_type in (\'d\',\'q\',\'o\',\'i\')),
  -- Guarantees that only the order_st table can reference rows having
  -- stmt_type = \'o\', only the invoice_st table can reference rows having
  -- stmt_type = \'i\', etc.
  unique (stmt_id, stmt_type),
  stmt_date date not null default current_date,
  cust_id integer not null -- references customers (cust_id)
);

-- order \"subtype\"
create table order_st (
  stmt_id integer primary key,
  stmt_type char(1) not null default \'o\' check (stmt_type = \'o\'),
  -- Guarantees that this row references a row having stmt_type = \'o\'
  -- in the table \"statement\".
  unique (stmt_id, stmt_type),
  -- Don\'t cascade deletes. Don\'t even allow deletes. Every order given
  -- an order number must be maintained for accountability, if not for
  -- accounting. 
  foreign key (stmt_id, stmt_type) references statement (stmt_id, stmt_type) 
    on delete restrict,
  -- Autoincrement or serial is *not* ok here, because they can have gaps. 
  -- Database must account for each order number.
  order_num integer not null,  
  is_canceled boolean not null 
    default FALSE
);

-- Write triggers, rules, whatever to make this view updatable.
-- You build one view per subtype, joining the supertype and the subtype.
-- Application code uses the updatable views, not the base tables.    
create view orders as 
select t1.stmt_id, t1.stmt_type, t1.stmt_date, t1.cust_id,
       t2.order_num, t2.is_canceled
from statement t1
inner join order_st t2 on (t1.stmt_id = t2.stmt_id);