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
