0

I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns.

SQLSTATE[HY000]: General error: 1005 Can't create table xxx.xxx (errno: 185 "Too many columns")

And because I've seen this question. Let me explain:

The company I work for is selling advanced sensors with algorithms in the firmware. With every new firmware, we want to compare it to the previous ones, to check if something still works, has improved or is now worse. For this, we have a number of test scenarios (200+). Each scenario consists of (400-2.000) measurements from which all are needed. I call a run through one scenario a record.

In my previous design I created a table for each record and I ended up with 50.000+ tables, which is not optimal, to say the least. In an attempt to normalize the data I created the following schema:

enter image description here

There is on table for each scenario (slug_1 and slug_2 here). And a idmap table to relate each record with the firmware and other important data (here shown as other_stuff). The problem now is, that I need 1-4 columns for each measurement in one scenario which means 8.000 columns max, which is a new problem.

Besides the id and the table name, there are no relations, nor search requirements on these large tables. Each request would consist of two rows from the same table to compare them.

Question:

  • Is there a better design to store the data?

I thought maybe I should store the measurements as json string or serialize the measurements array directly to binary. I could use a compression algorithm before storing and decompress the data after reading?

  • Are this many columns even that bad, if they are never searched?

If not, how can I get around the

errno: 185 "Too many columns"


Edit 1:

To answer some questions:

  1. Are all the tests pass/fail, or do you need to store more detail.

Every measurement consists of two values at the moment. Ok (bool) and Ct (int). Ok just stores if there was an error or not. The sensor can provide ~10 different errors, why the test failed, but I'm reducing this to true/false for now. If there is a need to store the error, I'll create an error table and store the relation instead. The second value Ct stores the time needed for this measurement. It is possible that there will be a 3. value in the future, but for now this is not needed.

  1. Does the type of detail depend on the test?

No, it doesn't in all scenarios the measurements are the same.

  1. Is there a Product or Sensor these tests apply to?

Yes, I store all sensors, firmwares, and other things in separate tables like other_stuff and relate it with the idmap table to the records of the tested scenarios.


Here some info what I'm working with:

Laravel 8 with php eloquent on a mysql database.

3

Is there a better design to store the data?

Probably. A measurement seems to be an entity in itself, and should be modelled like one. You will then need a one-to-many (or many-to-many, if a particular measurement applies to more than one scenario) relationship between scenarios and measurements.

| improve this answer | |
  • I updated my question to provide more detail. One measurement consists of a bool and an int. Is it really worth it, to store those in a separate table? – Darkproduct Sep 25 at 14:11
  • If it weren't, you wouldn't be asking this question. – mustaccio Sep 25 at 15:01
  • This doesn't make any sense as explanation. Lets say I have all measurements in one table and a one to many relationship to the scenarios. If I want to compare them, I have to acquire 2* the number of measurements in this scenario from the measurements table. Additionally, I have to store one more int to store the index, because I can only compare measurement data from the same position. I mean yes, this measurements table is a bit more normalized, but at the cost of 800 - 8000 more rows to retrieve, instead of just one single row. – Darkproduct Sep 29 at 11:11
1
  1. Is there a better design to store the data?

    Probably. Considering the query constrains (no searches on measurement data and only retrieving full datasets), I decided to store the measurement data in a json field. Supported by MySQL and Laravel.

  2. Are this many columns even that bad, if they are never searched?

    Here I still don't have a concrete answer, but I can say that it is not possible to store this many columns in MySQL regardless of the engine. The max column count is even smaller if you use utf8. [1]

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.