Tracking recipe parameters of production machines. Need help with database design

1

Hello,

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?

2

Based on lots of past experience, I'd suggest reconsidering the EAV model. I've blogged about it here:

Original blog (may be offline): https://weblogs.sqlteam.com/robv/2015/11/10/t-sql-tuesday-72-data-modeling/

Archived version: T-SQL Tuesday #72: Data Modeling | Most Valuable Yak (Rob Volk) Blog

That was based on numerous tables of nearly identical structure holding nearly identical data, that turned into JOIN spaghetti every time you wanted useful information about it.

The same DB schema I was criticizing in that article also suffered from tables with hundreds of columns, with associated history tables maintained by triggers. The difficulty there was determining which column of 150+ was different from one entry to the next. System-versioned tables were tested but didn't help in that regard, as they are still snapshotting the entire row

If tracking the history of parameter values is significant, it will be easier to audit EAV tables into a history table with timestamps, as they will only have a few columns. You can also accommodate new parameters, machines, production lines, etc. with an EAV structure. I understand you said you "know all attribute and they [won't get more]", but if that's actually true, it will be the first time in all database modeling history.

I'm working on a possibly demo schema to illustrate what you've described in an EAV layout. I'm not sure when I'll complete it but check back here within a day or two. Please also read the blog, even if the example doesn't match your situation, there's more support of EAV that probably applies.

Edit: changed "rows" to "columns"

1 Like
3

Thank you for your answer.
I read the link and it might actually be a good idea.

At the moment I am trying out an EAV table. I insert a new row in the table every time a parameter changes.
Machine name, recipe name, parameter name, value, timestamp.

I have not quite understood how to do it. Do you think I should use a system versioned EAV table and just update the parameter row? Or is my approach also ok?

You are right. For example, if we get a new machine there will be new parameters.

As an example, here is an Excel export of a recipe list from ONE machine. We have many formats/recipes 100ml, 125, 200ml, 400ml with different products. In this machine 40 different ones.
Screenshot 2023-07-29 203906

4

You are struggling with normalization. You should use your favourite searchenigine/chatgtp on Database normalization SQL Server to get some easy examples. This is very important part! Take a lot of time to create your model. Test it with sample data to make sure it works and if you have time enough write documentation and explain why you did something. It will help you in the long term.

Database normalization description - Office | Microsoft Learn

Database Normalization in SQL with Examples – SQLServerCentral

In this case I would create different tables, like Recipe, Parameter, Machine and Location. Then you create a table like this:

MachineID, LocationID, LocationCode

When you have 1 type of Machine on 20 locations you will have something like this:

MachineID=1, LocationID=1, MachineLocationCode= MACHINE01LOC01
MachineID=1, LocationID=2, MachineLocationCode= MACHINE01LOC02
etc.

This is important because when a machine is broken you can easily find the same machine of a different location, maybe you need to write some productionplanning and you need to split a production order to different locations of the same machine.

You call that table MachineLocation, MachineProductionLocation or something simular.

On that MachineLocation you have Recipes and Parameters, 2 extra tables. You can combine these table like this:

MachineLocationID, RecipesID, ParameterID, Value, CreatedBy, CreatedOn
1, 1,1,1, PeterDerMeter87, 30-07-2023 14:01
1,1,2,255,PeterDerMeter87, 30-07-2023 14:01
1,1,3,255,PeterDerMeter87, 30-07-2023 14:01

etc.

But it all depends on your situation, maybe Machine and Location is a little bit to much in your case for example. Just try to see the benefits of the model you create and think about what type of information should be stored in your database model.

1 Like