Как отслеживать стоимость продукта с течением времени для POS-системы?

Вот достаточно общий, но упрощенный пример того, как может выглядеть ваша схема.

CREATE TABLE products
(
  `id` int not null auto_increment primary key, 
  `name` varchar(13), 
  `price` decimal(12, 2), -- current sale price. You might want to extract it into it's own table `prices`
  ...
);

CREATE TABLE orders
(
  `id` int not null auto_increment primary key, 
  `date` date,
  ...
);

CREATE TABLE order_items
(
  `id` int not null auto_increment primary key, 
  `order_id` int not null, 
  `product_id` int, 
  `quantity` decimal(12, 3), 
  `cost` decimal(12, 2),
  foreign key (`order_id`) references orders (id),
  foreign key (`product_id`) references products (id)
);

CREATE TABLE sales
(
  `id` int not null auto_increment primary key, 
  `date` datetime,
  ...
);

CREATE TABLE sale_items
(
  `id` int not null auto_increment primary key, 
  `sale_id` int not null, 
  `product_id` int, 
  `quantity` decimal(12, 3), 
  `price` decimal(12, 2),
  foreign key (`sale_id`) references sales (id),
  foreign key (`product_id`) references products (id)
);

Вот демонстрация SQLFiddle.

Это дает вам возможность самостоятельно отслеживать свои расходы и продажи.

Один из способов расчета общего объема продаж, общей фактической себестоимости и маржи по продукту.

SELECT product_id, p.name, sales_quantity, sales_total, cost_total, sales_total - cost_total margin
  FROM
(
  SELECT product_id, sales_quantity, sales_total, SUM(
    CASE WHEN sales_quantity >= running_quantity 
           THEN cost * quantity
         WHEN sales_quantity BETWEEN running_quantity - quantity AND running_quantity
           THEN cost * (sales_quantity - (running_quantity - quantity))
          ELSE 0 
     END) cost_total
    FROM
  (
    SELECT s.*, o.cost, o.quantity, o.running_quantity
      FROM
    (
      SELECT product_id, 
             SUM(quantity * price) sales_total, 
             SUM(quantity) sales_quantity
      FROM sale_items
     GROUP BY product_id
    ) s JOIN 
    (
      SELECT product_id, cost, quantity, (
        SELECT SUM(quantity)
          FROM order_items
         WHERE product_id = i.product_id
           AND order_id <= i.order_id
         ) running_quantity
        FROM order_items i
    ) o
        ON s.product_id = o.product_id
  ) q
   GROUP BY product_id, sales_quantity, sales_total
) q JOIN products p
    ON q.product_id = p.id

Пример вывода:

| PRODUCT_ID |          NAME | SALES_QUANTITY | SALES_TOTAL | COST_TOTAL | MARGIN |
|------------|---------------|----------------|-------------|------------|--------|
|          1 | Coca-Cola 2.L |            150 |       187.5 |        145 |   42.5 |

Вот демонстрация SQLFiddle.

Вы можете видеть, что в примере первые 100 бутылок из 150 проданных стоят 100 долларов (1 доллар * 100), а остальные 50 - 45 долларов (0,9 * 50).

«Я хочу убедиться, что я очень точно отслеживаю свою прибыль. Поэтому я хочу, чтобы 100, которые я купил по цене 1 доллар, могли отслеживать их отдельно по цене 1 доллар и новый заказ по цене 0,90 доллара». - Это не обязательно. Вы должны рассчитать текущую среднюю цену и по ней рассчитать прибыль. У вас нет серийных номеров для каждой бутылки, и по этой причине, даже если вы хотите узнать прибыль по каждому предмету, вы не можете этого сделать. И с точки зрения владельца, важно знать общую прибыль от Coca Cola, а не то, что "с этой бутылки 0,50, а с той бутылки 0,45".