CODEWITHSUNDEEP
mysql
Gordon
Gordon

Reputation: 1651

mysql: table structure for this numerical database?

I am trying to define a table structure/schema for this simulation database. I have been thinking days about this and how to do normalization (not sure if it is even possible):

using a very broad example and the values could be anything

runset | input value | device name | temperature | voltage | measurement name | value

Presently, I can only think of 1st Normalization and that's it ...

Any ideas? a lot of the values can be unique inside this table and can reach up to 60 million rows.

Upvotes: 0

Views: 48

Answers (1)

James Anderson
James Anderson

Reputation: 27478

Actually what you have is just about right.

Its a table of observations so just put your data in a table just as you described.

You don't actually need to give a table a primary key, and in your case it doesn't look like you need one, but some people are deeply uncomfortable with this so a system generated "ID" column to act as primary key might be needed.

In addition you might optionally have (but only if you identify an actual need):

  • a "runset" table to hold details of each run such as date, time, operator etc.
  • a "measurement" table to fully describe each measurement type.
  • a "device" table to hold details on each device (make, model, serial number etc.)

Using "device name" and "measurement name" as primary/foreign keys would work just fine to there is no need to mess with generated "id"s on these tables.

A well designed schema should look simple and obvious, do not mistake complication and deviousness for good design.

Upvotes: 1

Related Questions