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

In reply to Slightly Off Topic - mysql, scalability, and splitting ':' strings by nmerriweather

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.