I will have a table with a line for an item per day - save the date, product ID, quantity sold and price sold (keep it even if it is also in the product table - if that changes, you want the value you actually sold, preserved). You can calculate the totals for each day of the day and the totals per day in the queries.
Tables:
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) );
Daily Reporting:
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';
Reporting for all days:
select saledate, sum(quantity * price) as total from sale group by saledate order by saledate;
Good main report for all days, with a summary line:
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;