I need a little help creating a database.
I have several production lines in our company.
We want to log the recipe parameters of each machine over time.
We have about 20 production lines.
Each production line has about 8 machines.
Each machine has between 50 and 300 parameters. Mostly integer and float.
Each machine has between 5 and 30 recipes for different products.
I would like, whenever a parameter changes (manually by staff), to record this value with a timestamp.
Currently this is done in an Excel list for each machine with tables for each recipe. Each column has a specific time (every Friday).
I have tried it with EAV but I think it will quickly become confusing. Besides, I know all the attributes and they don't become more.
Should I create a table with 50-200 columns for each machine? And if a parameter changes, always store ALL parameters in a new row with a timestamp?
Then I would have 20x8 tables with 50-200 columns each.
Can I use a system versioned table for each machine?
Probably 1 parameter changes per machine per day.
What is a suitable approach?