mysql feild with multiple user ids

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

mysql feild with multiple user ids

Post 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?
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: mysql feild with multiple user ids

Post 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
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

Re: mysql feild with multiple user ids

Post by jazz090 »

yes very interesting, didnt think of that
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: mysql feild with multiple user ids

Post 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
Post Reply