Database 库存数据库的最佳 struct

我会有一个每天每件物品一行的表-存储日期、物品ID、售出数量和销售价格(即使它也在产品表中-如果它发生变化，您希望保留您在产品表中的实际销售价值).您可以在查询中计算每个项目的合计和每天的合计.

表:

create table product (
  id integer primary key,
  name varchar(100) not null,
  price decimal(6,2) not null,
  inventory integer not null
);

create table sale (
  saledate date not null,
  product_id integer not null references product,
  quantity integer not null,
  price decimal(6,2) not null,
  primary key (saledate, product_id)
);

每日报道:

select s.product_id, p.name, s.quantity, s.price, (s.quantity * s.price) as total
from product p, sale s
where p.id = s.product_id
and s.saledate = date '2010-12-5';

全天报道:

select saledate, sum(quantity * price) as total
from sale
group by saledate
order by saledate;

一份完整的主报告，包括一行摘要:

select *
from (
    (select s.saledate, s.product_id, p.name, s.quantity, s.price, (s.quantity * s.price) as total
    from product p, sale s
    where p.id = s.product_id)
  union
    (select saledate, NULL, 'TOTAL', sum(quantity), NULL, sum(quantity * price) as total
    from sale group by saledate)
) as summedsales
order by saledate, product_id;