看起来您已经对所有其他事物(报价、订单、草稿、发票)进行了建模,其结构与所有其他事物相同。如果是这种情况,那么您可以将所有相似的属性“推”到一个表中。
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)
);
我认为这适用于您所描述的模型,但我认为从长远来看,通过将它们建模为超类型/子类型,您会得到更好的服务。所有子类型共有的列被“向上”推入超类型;每个子类型都有一个单独的表,用于存储该子类型特有的属性。
这个问题 https://stackoverflow.com/questions/4969133/database-design-problem及其接受的答案(和评论)说明了博客评论的超类型/子类型设计。另一个问题 https://stackoverflow.com/questions/4688972/different-user-types-objects-own-content-in-same-table-how涉及个人和组织。完后还有 https://stackoverflow.com/questions/5466163/same-data-from-different-entities-in-database-best-practice-phone-numbers-exa/5471265#5471265与人员配备和电话号码有关。
之后 。 。 。
这还没有完成,但我已经没时间了。我知道它不包括行项目。可能还漏掉了别的东西。
-- "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);