Database stucture for dynamic values

Question

I am working on a new project and I could use some assistance in designing the database.

Imagine these tables:

table groups
---------------------
group_id


table supplements
---------------------
supp_id
supp_name
supp_price


table recipes
---------------------
rec_id
supp_id
supp_weight 

So, let me explain.

The user can add groups (of animals) to his account. He can also add (food)supplements. With two or more supplements he can create a recipe.

So far no problems.

Then the user must be able to register the food he gives his animals. So I will need a table given_food or something. Normally I would use the rec_id and add it to the table, combined with the group_id and date, but

• the user should be able to deviate from the recipe. Meaning that it is possible that he feeds 90% or 110% of the recipe.
• the price from supplements may deviate from week to week (or even day to day). We want to register the costs, so if the user now changes the price, it should not affect the already given recipes from the past.

So the question is, how do I save this info at best in a database?

Answer 1

For the costs you should add a pricing table like

table prices
-----------
p_id
p_suppid
p_price
p_validfrom   -- date at which new price was given

and the group table should be equipped with an amount column:

table groups
------------
g_id
g_recid
g_amount  -- percentage of nominal amount (90 ... 110)

by joining these tables you should be able to organise all the data you want. The valid price for the 'supplement's (or ingredients) at a particular date (currentdate) can be found by doing

SELECT supp_id, p_price FROM supplements 
INNER JOIN prices ON p_id=( SELECT p_id   -- finds id of the latest
                            FROM prices   -- pricing info before currentdate
                            WHERE p_suppid=supp_id 
                            AND p_validfrom < currentdate
                            ORDER BY p_validfrom DESC LIMIT 1 )