Q:

How to integrate free or reduced products in mysql scheme?

I would like to add the support of reduced or free products in my MySQL database.
This is the current table structure:
products
+----+------+------+-----+-------+
| id | abbr | name | net | gross |
+----+------+------+-----+-------+

invoices
+----+------------+---------+
| id | created_at | paid_on |
+----+------------+---------+

invoices_products
+------------+------------+
| invoice_id | product_id |
+------------+------------+

The most simple option is to add a duplicated product entity with other pricing however as I got no big data on the database I would like to take an advanced approach which does not included duplicated data.

A:

The conclusion I got from my daddy who has knowledge about accounting is really smart:
In addition to the articles/products table you need to create a prices and a conditions table:
prices
+----+------+-------+-----+------------+
| id |  net | gross | net | product_id |
+----+------+-------+-----+------------+

conditions
+----+------+-------+-----+------------+
| id |  net | gross | net | percentage |
+----+------+-------+-----+------------+

We could now linke a price entity to a product and then move conditions on to it. This would avoid entites containing double data and it would allow flexibilty towards having multiple conditions applied to a product.

