Advice on database design - SQL recipe editor

Question

I want to build an SQL-based recipe editor for a brewery. I have n recipes:

• Every recipe is part of a category.
• Every category has a list of steps.
• Steps can be reused in the same category several times and in different orders.
• Every step has n set-points.
• Setpoints change based on the recipe.
• The same steps used more than once in the same category could have different setpoints within the same recipe.

I have 3 recipes (BeerA, BeerB and BeerC):

• BeerA and BeerB are lager (category), while BeerC is a stout (category).
• Lager has 5 steps
  (start, fermentation, wait, fermentation, maturation).
• All the steps have 2 set-points
  (time, temperature).

BeerA specific set-points could be:

• Start:
  [10seconds, 30 degrees]
• Fermentation:
  [2 days, 27 degrees]
• Wait:
  [3 hours, 25 degrees]
• Fermentation:
  [3 days, 23 degrees]
• Maturation:
  [5 days, 3 degrees]

BeerB has the same steps, same order, but with different set-points. BeerC has only 3 steps. What would be an example of table design? I was thinking:

• Recipe table
  (RecipeID, RecipeName, categoryID).
• Category table
  (CategoryID, CategoryName).
• Steps table (I need the reference to the category and to the recipe).

How can I deal with steps using different set-points? I believe I need also a Setpoints table, but how to design it?

Answer 1

-- Beer category BCT exists.
--
category {BCT}
      PK {BCT}

-- Brewing step STP exists
--
step {STP}
  PK {STP}

-- Step sequence number ST# for brewing beer
-- of category BCT is step STP.
--
cat_step {BCT, ST#, STP}
      PK {BCT, ST#}

FK1 {BCT} REFERENCES category {BCT}
FK2 {STP} REFERENCES step     {STP}


-- Note: ST# in [1,2,3 ..]

-- Beer BER is of beer category BCT.
--
beer {BER, BCT}
  PK {BER}
  SK {BER, BCT}

FK {BCT} REFERENCES category {BCT}

-- Step sequence number ST# of recipe
-- for brewing beer BER of beer category BCT
-- is at temperature TMP deg, for TIM minutes.
--
recipe {BER, BCT, ST#, TIM, TMP}
    PK {BER, ST#}

FK1 {BER, BCT} REFERENCES beer     {BER, BCT}
FK2 {BCT, ST#} REFERENCES cat_step {BCT, ST#}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename OPT# to OPT_NO.

Answer 2

What you have described is a reasonable starting point; I would have a "Beer" entity, to allow you to hold information about the beer, and I would also have some sort of versioning (or start/end dates) on the recipe - in case it ever changes.

So something like this (just an example, not meant to be a definitive model):