1

I have to design a database to store some production process measurements data. The data would be collected from PLCs. I would like to store this data on a remote server, to which all the machines have access. I would like to store the following data:

  • timestamp
  • plant
  • production line
  • machine
  • workpiece number
  • measurement unit
  • measurement type

Some machines share same measurements types, some have unique ones. Is it a good solution, that I would make measurement type column of type VARCHAR and let the PLC programmers free hands with naming new measurement types without my intervention to insert new entry in related foreign table and handing them the new ids? The expected count of unique measurement types is around 100. Is an index on this column a solution for later filtering and selecting from this table which is expected to have around 50 billion rows in a year? Probably also the size of the table would become a big issue.

EDIT: Should I also separate measurement value and measurement type to other table than part information?

Is there a way, that SQL server would take care of adding new measurement type to some internal table and handling the ids?

Hopefully I explained my question enough, otherwise write the question in comment.

Regards

gtu
  • 707
  • 1
  • 10
  • 22

1 Answers1

1

I would say create a new table measurement type as you mentioned the record is less than 100 then in such case make the Id column TINYINT which will save your space as well and helpful in creating the index.

Rajat Jaiswal
  • 645
  • 4
  • 15
  • And maybe [this](https://stackoverflow.com/questions/1997998/insert-data-into-tables-linked-by-foreign-key) would be the way to ease the work for PLC programmers – gtu May 18 '18 at 06:25