[Mailman-Developers] SQL in MM3 issues

[Chuq Von Rospach]

On Feb 5, 2004, at 9:04 PM, moron wrote:

>

> 1.  The user should be the core entity, not the list.  A list should be
> treated as an association with users.


not quite how I've implemented it. I prefer that the list and the user 
be peer objects. A user has an existance in the system, and there are a 
series of lists also on the system, and you connect them via relations. 
In SQL-ese:

user table
user_ID (primary key)
[suite of user data]

lists table
list_ID (primary key))
[suite of list data]

subscriptions
user_id, list_id

in a many-many relationship, no primary key. Neither side of that is 
subservient to the other, nor does it need to be. the relation is a two 
item table, both indexed. by creating the primary key to be those two 
fields combined and set as UNIQUE, you avoid duplicates.

Now, it's usually convenient to add more data to that table than just 
that relation; I normally also store a subscribed flag (ENUM Y|N), and 
a created/modified timestamp. why not use DELETE to remove a 
subscription? you can, but by doing an UPDATE (subscription = "N", 
modified = now()), you have data left around for reporting and 
statistics, and then you can delete them in one operation later when 
you want to optimize tables.

If you want to add aliases to a user record, this mode makes that easy, 
since you simply add which alias in the user record to mail to to the 
subscriptions table, and post to it; then accept from any email in the 
user's record for posting.

> 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.

agreed. a bounce on one system should affect all. undels flag the email 
in the user record, not the subscription. whether you fallback to a 
secondary alias (if we support those) or probe or something, it affects 
the user, not the subscription.

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


be careful here, down this road lies madness. trust me, umbrella lists 
and unions aren't as simple as this. And in reality, umbrella lists are 
a lot less needed than people think.