Sorry if the question is not exactly clear, but I'm having trouble expressing my problem in a concise way.
I've been asked to store a set of devices in a single MySQL table, and these devices have a varying amount of fields to represent on said table, with its correspondent column name. The datatype wouldn't really matter because all would be either strings or integer numbers, and heavily validated before being inserted. The need of it being a single table is that the amount of different devices is planned to grew significantly in the following months, and the structure already in place for relations is massive enough that a few more joins can make the user experience very painful.
For instance, the idea is to have something like this: (where row 1 & 4 share the same structure)
| id | device_type_id | col_1 | col_2 | col_3 | col_4 |
|---|---|---|---|---|---|
| 1 | 5 | 2 | val | NULL | NULL |
| 2 | 6 | 3 | val | val | 3 |
| 3 | 10 | 3 | val | val | NULL |
| 4 | 5 | 3 | val2 | NULL | NULL |
Is it possible to make a table like this and store the column names for it on a different table (in this case probably device_type) ? If so, how would I go about querying said tables?
Is there a better way of representing this structure?
Should I just fight to change the requirement for a table for each kind of device?
Any help would be appreciated!
