[wplug-web] schema for mailing lists

[David Ostroske]

From: "James O'Kane" <jo2y at midnightlinux.com>
Date: Wed, 19 Feb 2003 19:59:47 -0500 (EST) 
> Assumptions:
> People will have many addresses, but only one primary
> The column and table names I have below are vague

We'll just concern ourselves with the keys and the appropriate data fields for now.

> table mailing_lists {
> 	id
> 	other_mailing_list_foo_that_I_need_to_lookup
> }
> 
> table mailing_list_map {
> 	user_id
> 	mailing_list_id
> 	other_user_specific_options
> }
> 
> Then we can do a query to get the list of subscribers.
> select users.email_address from users,mailing_lists, mailing_list_map 
> where users.id = mailing_list_map.user_id AND
> mailing_list_map.mailing_list_id = mailing_lists.id AND mailing_lists.id = 
> <list wanted>;
> 
> That seems complicated to me, and I'm not sure my join is correct.
> Is this the right direction?

Your join is correct, James. Actually, the join to mailing_lists might be redundant, since the mailing_list_map would have the desired mailing list's ID in it already. But the additional join asserts that the desired list really exists.

It would be better to have another table, containing users' email addresses and some sort of flag designating a primary account. That would make the query more complex:

SELECT user_addresses.email_address
FROM user_addresses, users, mailing_lists, mailing_list_map
WHERE mailing_lists.id = <list wanted>
  AND mailing_lists.id = mailing_list_map.mailing_list_id
  AND mailing_list_map.user_id = users.id
  AND users.id = user_addresses.user_id
  AND user_addresses.primary = -1 --include this line if you just want primary addresses
;

(I hope I got this SQL dialect right. There might be JOIN clauses and boolean fields, but I don't know right now.)

But, if you want to associate a single email address with a given user in a given mailing list, then putting the email address in the _map table would make sense.

SELECT mailing_list_map.email_address
FROM mailing_lists, mailing_list_map
WHERE mailing_lists.id = <list wanted>
  AND mailing_lists.id = mailing_list_map.mailing_list_id
;

You can always join this to the users table again, if you only want certain users.

> If my assumption about one primary per person is wrong, we can add 
> multiple items to the _map table and add a column called email, and beef 
> up the select query.
> 
> -james

If you want multiple email addresses, but do not want them connected to particular mailing lists, then my first query would work. The second query would work better for mailing to just those addresses associated with a mailing list, but the concept of a "primary" email address would still need to be addressed.

--- David Ostroske
    eksortso at linuxmail.org


-- 
______________________________________________
http://www.linuxmail.org/
Now with e-mail forwarding for only US$5.95/yr

Powered by Outblaze