Currently I have a table with a column containing CSVs. I am not sure whether to normalize the whole table or not. The problem is this column, configuration, may contain up to 50 or more different types of values. For example in the table shown below it's 18, 20, but for other data in the same column it may be 0, 20, 21, 22, 23, 25, 26, 27, 40, 52, 54, 55 and so on, however these values are unique. They will never repeat.
I do not know what is the maximum number for it(it may vary) so that's why I kept it in CSV. I am currently have trouble normalizing it, or rather I am not sure whether I should even normalize it. Any help here?
id tester_type device_id board_id configuration
75946 UFLEX 997 220
44570 UFLEX 450 220 18,20
44569 UFLEX 449 220 18,20
44568 UFLEX 448 220 18,20
44567 UFLEX 447 220 18
Note: Configuration column does also contain empty values or empty spaces.
I do have to query against it so I guess I have to normalize it.
Yes, you do :)
If do create the table, does that mean I have to create for every possible configuration value?
An example of a normalised structure would be:
join table
==========
test_id configuration_id (spanning unique constraint)
------- ----------------
44570 18
44570 20
44569 18
44569 20
44569 20
44568 18
44568 20
44567 18
configurations table
====================
configuration_id
----------------
18
20
If you're using InnoDB, each column of the join table is also a foreign key to their respective parent tables.