2009/1/30 Nitsan Bin-Nun <nitsan@xxxxxxxxxxxx> > 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 > ;) I think you should go for a third relation table. This would be much better than using a comma seperated list and makes the tables less heavy (because the list table will not grow bigger while adding more users to a list) -eddy
