On Fri, Jan 30, 2009 at 8:37 AM, Andrew Ballard <aballard@xxxxxxxxx> wrote: > On Fri, Jan 30, 2009 at 3:38 AM, Nitsan Bin-Nun <nitsan@xxxxxxxxxxxx> > wrote: > > Hi there, > > > > I'm working on something similar to mailing list in which every > registered > > user has the ability to create a mailing list and join others mailing > lists. > > > > My users table is something like that (in short): > > > > CREATE TABLE IF NOT EXISTS `users` ( > > `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > > `email` VARCHAR( 255 ) NOT NULL, > > `password` CHAR( 64 ) NOT NULL, > > UNIQUE (`email`) > > ); > > > > And my mailinglists table is as following: > > CREATE TABLE IF NOT EXISTS `lists` ( > > `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > > `creator_id` INT( 11 ) UNSIGNED NOT NULL, > > `name` VARCHAR( 255 ) NOT NULL, > > `description` TEXT NOT NULL > > ); > > > > I must have relation between the users table and the lists table, but I'm > > not sure what is the best way to implement it. > > > > I have thought of using comma-separated ID's in special field in the > lists > > table of the users who have joined that specific list, but I have also > > thought of doing the same at the users table, then I had another solution > > which is to create new table: > > > > create table `relations` ( > > `listid` int(11), > > `userid` int (11) > > ); > > > > Which will contain the relations. > > > > I want it to be optimised so when I'm fetching the mailinglists that a > > specific user is registered (by user id) to and when I'm fetching the > users > > in specific mailinglist (by mailinglist id) it will be the fastest. > > > > The question itself is kinda newbie but I have to make sure it is as > > scalable as much because in few weeks it will have 10-20 unique > visitors/DAY > > ;) > > > > -- > > ?> > > Nitsan Bin-Nun > > Web Applications Developer > > nitsan@xxxxxxxxxxxx > > 972-52-5722039 > > > > I will second the other replies and say use the relation table. I > would only add that your relation table definition should include a > two-column primary key on both listid and userid, as this will ensure > that each user can be subscribed to a list no more than one time. > > CREATE TABLE `relations` ( > `listid` int(11) UNSIGNED NOT NULL, > `userid` int(11) UNSIGNED NOT NULL, > PRIMARY KEY `PK_relations` ( `listid`, `userid`) > ); > > > Another nice thing about this relations table is that you can later > expand it to include other things, such as defining the relationship > of a user to a list (moderator, administrator, subscriber, etc.) if > that becomes necessary. > > Andrew > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Third table is the way to go. From a performance perspective, with a packed field (values delimited in some way) you would likely be doing a LEFT LIKE or FULL LIKE (%value or %value%) statement which causes full tables scans since the index can't be used. That is much slower than querying a simple table with single values where the index can be used. -- Bastien Cat, the other other white meat
