Re: [PHP] Database Tables Relation Issue

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
.

Relevant Pages

  • Re: [PHP] Database Tables Relation Issue
    ... until now I didn't spent time on writing another table ... `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... CREATE TABLE IF NOT EXISTS `lists` ( ... two-column primary key on both listid and userid, ...
    (php.general)
  • Re: distribution list
    ... My experience is that you can expect a 5-10% bounce rate for any bulk ... mailing list you have, you should start considering a bulk mailing solution ... If you want to continue with a public folder, forget distribution lists. ...
    (microsoft.public.outlook.contacts)
  • Database Tables Relation Issue
    ... user has the ability to create a mailing list and join others mailing lists. ... `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... specific user is registered to and when I'm fetching the users ...
    (php.general)
  • Re: [PHP] Database Tables Relation Issue
    ... I'm working on something similar to mailing list in which every registered ... `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ... CREATE TABLE IF NOT EXISTS `lists` ( ... specific user is registered to and when I'm fetching the users ...
    (php.general)
  • Re: Database Tables Relation Issue
    ... user has the ability to create a mailing list and join others mailing lists. ...     UNIQUE ... specific user is registered to and when I'm fetching the users ...
    (php.general)