Mysql – How to structure this data? (Many columns currently.)

database-designmariadbMySQL

Working in MariaDB, we're trying to figure out how to store this data. All of our other tables have been normalized and are working nicely, but this has me stumped.

We have a number of objects and each object needs to have a state and two colors stored for each layer. The problem is that the upper limit of layers is around a hundred. Each object needs the data stored for each layer, as the objects are definitely not guaranteed to be identical in any way.

ObjID   | L0  |      L0C      | L1  |      L1C      | ... | L99 | L99C
8374629 | mmq | FFFF00,FFFF00 | mqm | 663FBA,34CF09 | ... | mqq | CDCDCD,00CDCD
8374630 | qmq | EE9329,00FF00 | qqm | 00FF52,ACF03D | ... | qqq | FFFF00,00F000
8374631 | mqq | 597842,F98520 | mmm | 05FF10,3726CD | ... | mmm | 05FF10,C0EAFF
8374632 | qqm | FADD03,A6A6A6 | qmm | DDAA11,763276 | ... | qmm | EEEEAA,EE9329
8374649 | mmm | BC1A44,FF00FF | qqq | 000000,FFCFFF | ... | mqm | 2323DD,01CD11

ObjID is always unique, but the rows are purged periodically as the object is no longer needed from the system. Lookup will always be based on ObjID. There will never be any filtering of the other rows (ie, selecting all where L0 is mmm).

The only other idea we've come up with is the idea of using strings to store the data and breaking them apart in the code that's using the data, but I assume that parsing a 1300 character string into a massive array would be both more inefficient and very inelegant. It also wouldn't allow easy editing of any given layer, which needs to be available by design.

The objID is referenced on another table, but otherwise the data here isn't replicated in any other location. Is this our best option for storing this many unique points? Some two hundred columns just seems like quite a lot.

Best Answer

To normalise this table you must remove the repeating groups and multi-value columns. The resulting table will be

objID
LevelNumber
State
Colour1
Colour2

Using the sample data from your question you would see

ObjID   | Level  | State | Color1 | Color2
8374629 |  0     |  mmq  | FFFF00 | FFFF00
8374629 |  1     |  mqm  | 663FBA | 34CF09
...
8374629 | 99     |  mqq  | CDCDCD | 00CDCD
....
8374649 |  1     |  mmm  | BC1A44 | FF00FF
8374649 |  2     |  qqq  | 000000 | FFCFFF
...
8374649 | 99     |  mqm  | 2323DD | 01CD11

The colors can be normalised into a separate table, too, giving two tables:

State              Color
=====              =====
objID              objID
LevelNumber        LevelNumber
State              ColourNumber
                   ColourValue

A table with several hundred columns is within the capabilities of any DBMS. It may be strange but it is not nesessarily wrong.

Related Question