nmerriweather has asked for the wisdom of the Perl Monks concerning the following question:

I'm in the process of building a site w/mod_perl and mysql

sorry for the slightly ot post, but i'm in the middle of a logic equivelant writers-block, and can't figure out how the best way to handle something.
i've got 'working models', but they're not good. And could use a perlmonk's advice.

My current implementation will work for now, but is not scalable at all. not in the least bit -- and i want to do this right.

Someone can be a subscriber or an account-holder.
Subscribers can sign up to receive mailing lists.
Account holders can send messages to mailing lists they control, or edit 'nodes' they are granted permissions on
Each mailinglist and node has a unique id linked to its record.

Currently:
Each subscriber has a record in the 'profile_mailinglists' table -- which looks like
user_id mailinglists_1 mailinglists_2
columns mailinglists_# are created as needed by perl, containing a 'SET (1..50)' where 1-50 is a block of list ids

Each account holder has a record in their 'profile_account' table which looks like
admin_id mailinglists nodes

i'm just throwing in ':' delimited strings in mailinglists & nodes, then splitting them in perl and doing a subsequent query to get their data

but that has me thinking... this doesn't seem 'good' to me. its a hack. and a poor one.

i thought about having another setup, but it seemed too inefficient..
table_subscriptions
user_id | mailinglist_id

table_node_privileges
user_id | node_id

the subscriptions table could work:
i could just select the ids where mailinglist_id ='$', or unsubscribe 'delete where user= & list='
and create an index on user_ids - as i just need 'speed' on presenting a users profile/subscriptions -- pulling a list of subscribers can take as long as necessary

but the node_privileges seems too inefficient:
as a user can have multiple nodes - and a node can have multiple users, i'd need 2 indexes, both of which would contain non-unique values. seems like a lot of trouble.

the method of splitting :-delimited strings in perl and requerying works well for speed, but isn't too scalable
plus it wastes a lot of disk space -- i'm using CHAR() opposed to VARCHAR() for speed/reliability, so there's a lot of empty space there

has anyone dealt with something like this?
what i'm working on kinda resembles the perlmonks system at this level, but i have no idea how the nodes are handled, and couldn't figure anything out from the evertyhing engine i dl'd

  • Comment on Slightly Off Topic - mysql, scalability, and splitting ':' strings

Replies are listed 'Best First'.
Re: Slightly Off Topic - mysql, scalability, and splitting ':' strings
by krisahoch (Deacon) on Sep 05, 2002 at 22:32 UTC

    I think that you may want to use a join table. If both your 'OWNER', and 'NODE' entities have a primary key (unique number), then creating a join table that has a foreign key for each table would create a unique join table. A NODE could be associated with any number of OWNERS and an OWNER can have any number of NODES 

    +--------------+              +-------------+
| OWNER        |              | NODE        |
+---+----------+ <---+  +---> +---+---------+
|PK | OWNER_id |     |  |     |PK | NODE_id |
|   | other    |     |  |     |   | other   |
+---+----------+     |  |     +---+---------+
                     |  |
               +--------------+
               | MAILING_LIST |
               +---+----------+
               |FK | OWNER_id |
               |FK | NODE_id  |
               +---+----------+

    [download]
    Join tables work great for associating data from one table with data from another.
    Updated: Oops. misnamed an element.
    Updated: Forgot to name the join table:\

    HTH - Kristofer

[reply]
[d/l]
Re: Slightly Off Topic - mysql, scalability, and splitting ':' strings
by anithri (Beadle) on Sep 05, 2002 at 22:42 UTC

    You should really use a seperate table for subscriptions. Index it by user_id as this is a write-little read-many type table. Ultimately I'd look to do something like...

    User_table
    user_id
    password
    Name
    Email
    ...
    Mailinglist_table
    Maillist_id
    Name
    ...
    Subscription_table
    Maillist_id
    User_id
    Admin_priv_table
    user_id
    Maillist_id

    selecting and displaying a user's profile would be something like.

    $uid #contains usersid
($uname, $upass, $uemail) = $dbi->fetchrow_result("select User_ID, Nam
+e, Password, Email from User_Table where User_ID = '$uid'");
#output basic info

$sth = $dbi->prepare("select MailListName from Mailinglist_table, Subs
+cription_table where Subscription_table.MaillistID = MaillingList_tab
+le.MaillistID  AND Subscription_table.User_ID = '$uid')
$sth->execute;
while (($subname) = $sth->fetch_row_array) {
    #OUtput MaiList Name
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom