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.