Page 1 of 1

mysql feild with multiple user ids

Posted: Sat Jun 06, 2009 2:31 pm
by jazz090
i have a table called books with a feild called user_ids which correspond to the books authors account. now seeing as a book can have multiple authors; this left me no choice but to store mutiple users ids in one column. for example a row in the book table may look like this:

--------------------------------------------------------------
book_id | user_ids | title | isbn | publisher | date |
--------------------------------------------------------------

NA | ;12;5;2;3;1; | NA | NA | NA | NA |

now lets say author with primary key 5 logs on to the site and wants to see the books he has contibuted to
now seeing as i cant no loner say: SELECT * FROM books WHERE user_ids = 5, i have devloped another way:

Code: Select all

SELECT * FROM books WHERE user_ids REGEXP ';{author_user_id};'
you may be wondering why i have semis all over the place: its becuase the record would appear like this originally: 12;5;2;3;1
but i had to specify the regex as ;?{id};? which returned data unexpectedly which is why i have this method now. but beucase the last method did this
its leaving me totaly paranoid about this new method in which i have semis before the id starts and once it finishes. so my question is this: is this a good way to access the data or can this method also return unexpcted results?

Re: mysql feild with multiple user ids

Posted: Sat Jun 06, 2009 4:48 pm
by Raph
Saving multiple values in the same cell is a very big no-no. At times it *can* be useful, but never if there's a search involved. I'd strongly recommend you to create another table where you save the author_id seperately, much like you would have a seperate table for comments on a blog-entry. If there are multiple authors for one book, you'll do one entry per author.

id | author_id | book_id
1 | 5 | 37
2 | 6 | 37
3 | 17 | 37
etc.

When an author wishes to see his books, you'll search this table for his author_id. If you have additional information associated with each book that you want to display, you can link the two tables together.

edit:
cleanup

Re: mysql feild with multiple user ids

Posted: Sat Jun 06, 2009 4:58 pm
by jazz090
yes very interesting, didnt think of that

Re: mysql feild with multiple user ids

Posted: Sat Jun 06, 2009 9:06 pm
by califdon
Raph is 100% correct. The very first rule of data normalization, the fundamental design rules for every relational database, is that values in any column must be single valued, sometimes called "atomic". What you have there is clearly a many-to-many relationship (one author may write many books, one book may have more than one author), which always requires 3 tables to represent: the 2 entitiy tables (authors and books) and the "joining table". Check out these references:
http://r937.com/relational.html
http://forums.aspfree.com/microsoft-acc ... 08217.html
http://www.tekstenuitleg.net/en/article ... -many.html