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.
First, if field 2 is unique, then it should be declared as the primary key (or, at the very least a unique index).
Secondly, the fact that you have multiple values in field3 tells you that field3 should become a separate table linked back (related) to the first table using some unique value (which we just decided is field2).
So, your database creation script would look like this:
CREATE TABLE MainTable (field2 VARCHAR NOT NULL PRIMARY KEY, field1 VARCHAR)
CREATE TABLE OtherTable (field2 VARCHAR REFERENCES MainTable, field3 INTEGER)
You might also consider adding a separate primary key value to the second value or make field2 and field3 together a primary key.
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
- http://www.ebruni.it/en/software/os/webgui/sqledit/examples/master_detail.htm
- Parent Child table record - Building SQL query
精彩评论