Re: Database Tables Relation Issue

Yes that's right, until now I didn't spent time on writing another table because my job was to develop some internal system to manage the company projects & HR (we are too small, it won't make the difference). Well umm thanks, I'm going to write the blueprints for the tables :P On Fri, Jan 30, 2009 at 4:10 PM, Bastien Koert <phpster@gmail.com> wrote: > > > On Fri, Jan 30, 2009 at 8:37 AM, Andrew Ballard <aballard@gmail.com>wrote: > >> 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 >> >> -- >> 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 > -- ?> Nitsan Bin-Nun Web Applications Developer nitsan@binnun.co.il 972-52-5722039