SQL data normalization chalange with example

Question

I have data normalization challenge here and would be really grateful for some tips and directions.

I have excel file with very messed up data which I would like to transcribe in to SQLite database. I have cells like this (dummy example data):

PRINTER    COLORS_TO_CHANGE 
canon1     red and blue, purple, brown/green 
hp1        yellow/green, red, blue or purple 
canon2     brown or black/red, blue or green and purple 
epson1     red, green, blue

I normalized data in to 3 tables:

**tbl_printer**
printer_id    printer
1             canon1
2             hp1
3             canon2
4             epson1

**tbl_colors**
color_id     color
1            red
2            blue
3            green
4            brown
5            purple
6            yellow
7            black

**tbl_grammar**
grammar_id      grammar
1               and
2               /
3               or

What I wish to have at the end is list of colors/color combination for each printer. Something like this:

Canon1:

• red and blue
• purple
• brown/green

I get it that I should create groups (for each bullet on list) and relate printers to those groups. And then create combinations from colors and grammar. And finally relate those combinations to groups. Something like this:

tbl_printer_groups
group_id   foreign_printer_id   bullet_group_id
1          1                    1
2          1                    2
3          1                    3
4          2                    1
5          2                    2
6          2                    3
7          3                    1
8          3                    2
9          4                    1
10         4                    2
11         4                    3

And after that I create combinations that fit in to each group. And this is the part where things stop for me. If tbl_colors and tbl_grammar would be the same table, lets say tbl_thesame in which data from tbl_grammar fits just bellow data from tbl_colors, I would do something like that:

tbl_printergroups_to_combinations
combination_id      foreign_group_id       foreign_thesame_id        order_in_combination
1                   1                      1                         1
2                   1                      8                         2
3                   1                      2                         3
4                   2                      5                         1
5                   3                      4                         1
6                   3                      9                         2
7                   3                      3                         3
8                   4                      6                         1
9                   4                      9                         2
10                  4                      3                         3
11                  5                      1                         1
12                  6                      2                         1
13                  6                      10                        2
14                  6                      5                         3
15                  7                      4                         1
16                  7                      10                        2
17                  7                      7                         3
18                  7                      9                         4
19                  7                      1                         5
...                ...                    ...                       ...

Where I get lost is how do I do this from 2 separated tables. I can't group data in one, I can't put foreign keys and relations in same table in different columns since some bullet groups don't have tbl_grammar info and it would be bad practice to leave cells empty/fill them with something nonsensical.

So only choice is to separate data in another parent-child relation but then I'm not sure how to make sure to impose order of discrete pieces of information when I query data and finally print a string.

Any kind of directions/help would be much appreciated.

Thank you!

Answer 1

Add each combination of colors to the tbl_colors table if that does not work then you could do something like this:

PrinterId ColorId GrammarId GroupId
1         1       1         1
1         2       1         1
1         6       0(add none) 2
1         4       2         3 
1         3       2         3       

You can even take out the grammarid column from the above and add another group_grammar_mapping table

Group_Grammar_MappingId GrammarId GroupId
1                       1         1   

PrinterId ColorId Group_Grammar_MappingId
1         1       1         
1         2       1         

Answer 2

I do not understand why you need to have tbl_grammar? Why can't each color combination be in fact a separate colour in tbl_colors? E.g.

color_id     color
1            brown or black/red
2            brown
3            black/red

etc.