Search code examples
sqlnormalize

Normalize table

I am wondering how to normalize table like this:

field1|field2|field3
text1 |txt2  | 1,2,5
other1|other2| 1,8,7

field1 will repeat many times , field2 is unique(won't repeat) , field 3 - users id. Something like favorites.

Solution

  • Give a primary key for this table and create a new one with field3+this key.

    Master table (your original table after the modification)

    field1 | field2 | fID
...    | ...    | 1
...    | ...    | 2

    Favorites table:

    fID | field3
1   | 1
1   | 2
1   | 5
2   | 1
... | ...

    Selecting data

    You can use JOIN or just a WHERE to get the results. Let's say

    SELECT field3 FROM Favorites WHERE fID = 1

    gets

    1,2,5

    So if I'm not wrong your question is related to the master-details/parent-child pattern.

    Resources