Brtdku

I'm building a Database (mysql) to collect Data from various Clients.
The Dataset i get will be a Timestamp (Datetime) and 600 Values (Float Numbers or boolean Variables).
Every Client generates a Dataset every 5 Mins.
The Purpose of collecting all this Data is to analyse it later on, filtered by the Datetime and the Client.

My first Idea was to make a Table with a lot of columns, something like this:

¦ id ¦ timestamp ¦ client_id ¦ val_1 ¦ val_2 ¦ ... ¦ val_600 ¦

Where:
'id' is a auto-incrementing integer primary key Field,
'timestamp'a datetime field,
'client_id' is integer Field which refers to the Client in another Table,
'val_n' is a text Field, this is to stay flexibel, because not every Client provides the same Dataset (some have only Floats, some may have 200 or 300 of them and some only have boolean Values or any Number of them. The Structure of the Dataset is defined in another Table which also can be refered via the 'client_id').

My Database with it's Tables is like in the Normal Forms described and works i as whish.
But as i readed a lot about the Design of a Database i saw a lot of flaming (mostly here) against People who asked to make a Table with more than like 20 Columns. As it seems there can be a lot of Problems if doing so.

But i don't now how to do different, the only Thing i can think of is this:

¦ id ¦ timestamp ¦ client_id ¦ float_data ¦ boolean_data ¦

Where:
'float_data' and 'boolean_data' would both be a text Field and inside this two Fields would be a serialised dictionary like: {"1": 23.4, "2": 87.2...}.

In this Case i obviously wouldn't use the ammount of Columns.
The downside of this is, if i want to know Value 46 from a Client and need the a Set of this Data for 10 Days (would be 2880 Rows), i had to deserialize every of this dicts and select the right Value according to it's Key.
This seems not really Practicable to me.

The thing is im pretty new to Databases and i fear that this Table-Designs will be a Problem one Day.
Does anybody know a better Solution than my two approaches or do i worry to much and should run it like this?

The downside of this is, if i want to know Value 46 from a Client and need the a Set of this Data for 10 Days (would be 2880 Rows), i had to deserialize every of this dicts and select the right Value according to it's Key. This seems not really Practicable to me.

What if mysql could do that work for you? Recent versions of mysql support json data types https://dev.mysql.com/doc/refman/8.0/en/json.html, which in my experience is a great fit for times when you need flexibility on incoming data.

https://dev.mysql.com/doc/refman/8.0/en/json.html

It mostly takes care of the downside because you can query the json data as if it were columns - not as quickly as truly normalized tables which are very efficient, but still probably more quickly than digging through n columns to find the appropriate data (which is usually very slow, both because the format defeates with the techniques databases use for efficiently handling data , and because there's a lot of data per row that can't meaningfully be indexed) .

You'd still want to normalize all the data possible. Things like client id, received date, etc probably make sense to be normalized. The rest can be sent as a json object and stored as-is in the database, queried relatively efficiently at runtime as use cases require.

Plan A: If the 600 values are "the same"; that is, it's an array of similar readings. And you will be interrogating some of them. Then make a second table with id (from the main table) plus 1..600.

Plan B: You won't be using any MySQL queries against the values. Dump them into a JSON string (or other serialization), and make that a column. Compress it in the client if practical -- this will save about 2/3 of the space.