Topic: database design

[micah]

so, I currently have a bulk mail email system I created for a website where users subscribe to differnt mail lists.

set up the database was with two tables.
One table holds a list of all the news letter types and contains a snippet of SQL to filter what sets of people can receive each letter.
for example, a row may look like this:

Code: [Select]

+-----+----------------------+---------------------+
| ID    |   NewLetter            |  Filter                    |
+-----+----------------------+---------------------+
|  1    |  General                 |   1=1                    |
+-----+----------------------+---------------------+
|  2    |  Men's News           |   `sex` = 'm'         |
+-----+----------------------+---------------------+
|  3    |  Women's News      |   `sex` = 'f'           |
+-----+----------------------+---------------------+
|  4    |  Only Micah            |  `user_ID` = 1139 |
+-----+----------------------+---------------------+
Then, in my members table there is a column called something like "subscriptions" which is just a pipe-deliminted string of news letter ID's that the user has subscribed to. like:

Code: [Select]

|1|2|3|
When my script runs to send the mail to the Women's News subscription list it has a query this to figure out who gets the letter

Code: [Select]

SELECT email_address FROM members WHERE `subscriptions` LIKE '%|3|%'
The part thats a little trickier is when users are prompted with what newsletters they can subscribe too, I do a several queries like this:

Code: [Select]

<?php
$id = 234223; // member's id value previously set

$getLists = mysql_query("SELECT * FROM emailLists");

for($i=0; $i<mysql_num_rows($getLists); $i++){
 $list = mysql_fetch_assoc($getLists);
 $parameters = $list['Filter'];
  $checkList = mysql_query("SELECT mailLists FROM members WHERE id = $id AND ($parameters)" );
  //do something with these results
}
?>
I'm getting ready to add a new feature to the site that lets admin users create new newsletter types on the fly and set an initial subscriber list.  The problem is that some of these filters could be more complex. instead of just "all women" where "sex = f" it could be 30 or 40 specific people.   like "id = 1139 OR id = 1257 OR id = 1523 OR id = 1611 OR etc etc"

So I have TWO questions on how I should really be doing this.

the first is pretty easy.  I don't think I should be using the |pipe| delimited list of email id's in the member table. I think best practice dictates that I should really have a separate table linking users and their subscriptions with basically just 2 columns: the user id and the  id of the newsletters they're subscribed to.  Then there would just be a row for each subscription.  So if there are 1,000 users and 20 news letters there could potentially be 20,000 rows in this table.  Does this make sense? is it the best thing to do?

the second is a bit more complex.  How should I set up the filter restraints on the newsletters?  With the SQL code snippet, I could fairly easily subscribe all users to their newsletter automatically (and then let them unsubscribe later) with something simple like "UPDATE members .... WHERE $whatever_the_filter_parameters_are"

Should I continue to use the SQL code snippt and have really long code snippets specifying dozens of unique ids?  Any suggestions on how else I could do this?  One thought I had was to keep the code snippets - which still come in handy for general uses but then create yet another table for specific lists, again with two columns "newsletter id" and the id of each person eligible to receive that newsletter.  Then set the SQL code snippet filter to something like  "id IN (SELECT user_id FROM newsletters_individuals WHERE newsletter_id = N)"

Thanks for your thoughts!

[Mike]

Honestly for the design I would ignore the case where you are picking out particular people.  That can be handled just by putting them directly in a subscriber's table and then set the criteria to some false condition to prevent future people from seeing it.

How many newsletters and how many filter items are we talking here?  I'm not a big fan of putting the SQL in the database because then you can't use it directly in the queries in one shot.

Perhaps something like:

Newsletter
------
id
title
other
open_subscription (bool)
gender (ENUM it as male, female, both)
min_age (tinyint)
max_age (tinyint)
something else
another thing
whatever

subscriptions
------
member id
newsletter id

For the subscriptions table it is simple normalized data.  For newsletters that are for specific people only you can put them in this table and then set the open_subscription to false.

When a new person registers you can do something like:

Code: [Select]

SELECT id, title
FROM newsletters
WHERE (gender = 'BOTH' OR gender = '{USER GENDER}')
  AND {USER AGE} BETWEEN min_age AND max_age
  AND open_subscription

Now if you have some complex criteria you can do something like this:
Newsletter
------
id
title
other
open_subscription (bool)
criteria (text)

subscriptions
------
member id
newsletter id

Here criteria holds a serialized PHP array.  You then do:

Code: [Select]

SELECT id, title, criteria
FROM newsletters
WHERE open_subscription

You then unserialize criteria and use that to drive some logic and throw out the ones they don't qualify for.  You then display the list and let them choose.

[micah]

Thanks Mike for your well thought out response.

I think thats a pretty good solution. 

Currently, there are less then 300 users and about 5 "newsletters" -- the user base will expand slowly over time (maybe 50 a year) but I invision there being dozens of newsletters for various subsets of the userbase.

Just to recap and make sure I got all that, I should:

1) In the newsletters table, define specific parameters that easily match columns already set in the users table such as gender, age, and location.
2) If there are more complex parameters, have a column containing serialized PHP code to be run after the SQL query brings back a larger dataset that can be filtered via the PHP script, not the SQL.
3) Create a lookup table of specific users linked to specific newsletters for any newsletter that is not flagged as being open to everyone.

 

[Mike]

re: #3

I would actually use that table for all subscriptions.  That way people can easily opt-in / opt-out just by adding or removing them from that table.  Use the criteria to populate the subscription table when the newsletter is created.

When you want to send a newsletter you can do a simple table lookup like:

Code: [Select]

SELECT name, email
FROM subscriptions
LEFT JOIN members USING (member id)
WHERE newsletter id = ID


[KnuckleBuckett]

Don't forget to parse the apocalyptic subscription table.

[ober]

*Knuck bitchslap++*

[micah]

I think, instead of a complex array of criteria that needs to be parsed (even though I really liked the idea,) that I'm going to allow users to be associated into groups. This will probably come in handy elsewhere in the site at a later time anyway.

So, I'm going to go with 4 tables (plus the users table)

Groups
ID
name (name of group, like, "Cool Kids Table")
leader (optional user id of leader, so said person has permission to send newsletters to group's subscribers)

Group_Members
group_id
user_id

NewsLetters
ID
Name  (name of news letter, like, "Cool Kids News")
Gender (optional known parameter set in the users table)
Some_Other_known_parameter
Group_id

Subscriptions
newsLetter_id
user_id