Re: Database Tables Relation Issue

On Fri, Jan 30, 2009 at 3:38 AM, Nitsan Bin-Nun <nitsan@binnun.co.il> 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@binnun.co.il > 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