Optimal database structure

Question

I am developing an production control application for a local distillery, and I am having issues structuring the database.

A core feature is the users ability to create spirit types, these types all have an associated production process. Each process can have multiple steps. There are 7 different types of steps.

When the user creates a "batch" of some spirit, a spirit type will be chosen and the user will be presented with a "wizard" following the production process defined. At each step, the user will input information related to that step.

What I came up with was this many-to-many relationship:

spirits (spirit types)
- id
- name

steps (steps in the production process)
- spirit_id
- state_id

states (names of all possible steps)
- id
- name

While this worked for the creation of the spirit types, it lacked when it came to filling in the data. Since each step in the production process step takes different data:

distillation
- abv
- mass

dilution
- abv
- water

filtration
- density
- volume

What would be an optimal structure for an application of this sort?

Answer 1

for me you should use a few some table for manage a master/detail relation between process and stess .. a relate this to similare class of steps

activity (class of steps in the production process)
 - activity_id 
 - name

process_phase  (master table for process steps)
 - process_phase_id
 - description 
 - activity_id
 - spirit_id

process_phase_detail  (detail table for process step )
 - process_phase_id
 - detail_id
 - state_id

PS -- data modelling (without severe specification) is an opinion based area ..