Re: Database Tables Relation Issue

2009/1/30 Nitsan Bin-Nun <nitsan@binnun.co.il> > 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