[Mailman-Developers] SQL in MM3 issues

[moron]

On February 5, 2004 08:08 pm, Kevin McCann wrote:
> I want to do this same kind of thing with Mailman 3. And so I want, at
> the very least, to have those three aforementioned tables of data:
>
> lists
> members
> messages

Ok.  Here is how I see these on a general level:

members
- a member belongs to any number of lists
- a member has access permissions that denote status which can be assigned 
either on a per list or per group basis

lists
- list are arbitrary collections of members 
- lists can be summed into larger groups on the fly as appropriate
- lists do not have to be static (for example "all members since this date")

messages
- messages are tied to a specific user but associated with a given list

Important directives:

1.  The user should be the core entity, not the list.  A list should be 
treated as an association with users.   This makes the flow of "what lists am 
I on?" and "what has Jane Doe posted?" a natural one. 

2.  Bounce handling should no longer have anything to do with a specific list.  
All bounces should be for the entire installation.  You should have one 
password per installation, one log in per installation, etc.

3. Messages can be to more than one list since "lists" can be unions.  This 
solves the "how do I notify everyone?" problem.

So with these ideas in mind (obviously far from exhaustive, I am just thinking 
out loud) you would need the following tables:

members
- holds profile, global permissions, global status

lists 
- holds list type (static, dynamic, union)
- holds list status (active, inactive)
- holds list attributes (moderated, unmoderated, etc.)
- holds description details
- holds template customizations

membership
- holds list id
- holds member id
- holds relationship (read only, post, moderated, last post, admin, whatever 
you want)

messages
- holds member id
- holds message body (reference to file?)
- holds message headers
- holds message status

message_to_list
- holds message id
- holds list id

Now I can think of lots of other stuff as well but I am just not sure how much 
you plan to push into MySQL for MM3.  If *everything* configuration wise is 
going unto MySQL, then you will need further tables too for the glue and for 
some other settings.

What specific fields need to be in these tables of course is a whole other bag 
of hammers.

Cheers

-- 
---> (culture) http://industrial.org : (label)  http://deterrent.net
---> (community) http://ampfea.org : (hire me) http://codegrunt.com
---> (send EEEI news to) infosuck at industrial.org
---> Whomever dies with the most URLs wins!!!!!!!!!!!!!