Best structure for inventory database

database, structure

Solution

I'd have a table with a row per item per day - store the date, the item ID, the quantity sold, and the price sold at (store this even though it's also in the product table - if that changes, you want the value you actually sold at preserved). You can compute totals per item-day and totals per day in 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)
);

Reporting on a day:

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 on all days:

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

A nice master report over 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;

Problem

I want to create a small database for my inventory but I have some problems on picking a structure. The inventory will be updated daily at the end of the day. The problem I am facing is the following. I have a table for my products, having an ``` id, name, price, quantity. ``` Now I have another table for my sales, but there is my problem. What kind of fields do I need to have. At the end of the day I want to store a record like this: ``` 20 product_x $ 5,00 $ 100,- 20 product_y $ 5,00 $ 100,- 20 product_z $ 5,00 $ 100,- 20 product_a $ 5,00 $ 100,- ------------------------------------------------- $ 400,- ``` So how do I model this in a sales record. Do I just create a concatenated record with the product id's comma separated. Or is there another way do model this the right way.

Original source