Mailing List, Structure Advice

holstead

My client wants to be able to search his client database by date, select multiple clients and send them promotions, and then have the database remember that those clients were sent an email on that date.

How would you recommend I store multiple dates in the client database to remember that John Doe was sent an email in July and September, and then have that searchable.

For example: Jack and Jill were sent promotion emails July 15th, John was sent a promotion July 15, and August 15. Admin wants to search the database for people who have not be sent an promotion since august.

I'm just looking for advice on how to make field to store multiple dates (probably just text delimited by commas) and convert it back to dates so I can use search functions on it.

AstroTeg

Originally posted by holstead

I'm just looking for advice on how to make field to store multiple dates (probably just text delimited by commas) and convert it back to dates so I can use search functions on it.

Ouch! This would be painful and defeat the purprose of having a database engine.

What you need to do is look into many-to-many joins. In English, you have one table of many people linking to another table with many dates/mailings. This sounds like 2 tables, but its actually three:

Table 1: The user table. From the sounds of it, you already have this. Lets assume you have a unique user ID. Lets call it userID for giggles.

Table 2: The promotions table. This you may not have. This table would contain a list of all the promotions that get sent out. It doesn't care WHO they're going to. It just keeps track of a promo name and a few dates (maybe the date the promo was created and/or the date it was emailed to everyone). This table would have its own unique ID per promotion. Lets call it promID.

Table 3: (the key to making this work) This table will be a cross reference table between users and promotions. Lets name it xref_UserPromo. It'll just contain a list of IDs like this (it will also assume 1 user will get sent 1 promo at a time):

xref_UserPromo:
userID (indexed)
promID (indexed)

That's it.

When you go to send your mailings (or better yet, to find your list of users you will mail to!), populate this table with the userID and the promo ID you're working with. Now you create a link between a user and a promo. This is something you can query off of all day long.

A query might look like this:

SELECT userID, userName, (etc) FROM User INNER JOIN xref_UserPromo ON xref_UserPromo.userID = User.userID INNER JOIN Promo ON Promo.promID = xref_UserPromo.promID WHERE (you can get creative here)

The above query is untested, but should work. Now with the where clause, you can ask it to show all promos sent for a particular user. Or you can have it show all users you sent to for a particular promo. Or you can browse all users and all the promos you sent to. Or you can look for all users you have sent promos for. You can get pretty creative with the where clause here.

Make sense?

holstead

Perfect! thanks much for the help, the inner join with the xref table would be perfect and make it much easier than what I was thinking.

Good time to start practicing my joins.

Thanks much!!

holstead

Ok so every time an email is sent to multiple clients, I store that promo into the promo table, along with the date. Then I populate the xref table with the promID and userID. What happens if we send the user an second email? There will be 2 rows with the same userID except a different promID.

So in this xref table I'd have alot of userID's duplicated with correspoding promoID's. To find customers who HAVE NOT receieved lets say promos 1 and 2, I'd have to join User table and xref, because xref wouldn't have the ID numbers of clients that havn't gotten anything.

Do I have the right idea sofar? My client has about 900 customers. If in the next 3 months we send them each 3 promos, the xref table would have 2700 rows? ouch?

AstroTeg

For multiple emails, we'll need to define exactly whats going on to ensure this all makes sense.

Lets say I'm one of the 900 email people. So AstroTeg has a userID of say 145.

Now lets say you have 10 promos (starting from promID 1 up to promID 10). AstroTeg qualifies for 3 of them (they may get emailed out at any time or over a span of a huge amount of time - doesn't matter).

Your xref table might look like this:

userID | promID
145, 5
145, 9
145, 2

And this is fine. What's unclear in your description is when you say "receive a 2nd email". Is it possible AstroTeg will receive 2 emails for promo #5? (for example) If so, this will make things just a little more complex. In this case, you could add an auto increment ID to the xref table to allow for duplicate userID/promID combos. But without it, it ensures only one email goes out per person per promo, which usually is a good thing in this scenerio.

Addressing your issue of those who HAVE NOT received email fro promos 1 and 2, you could do the following:

SELECT userID FROM User LEFT JOIN xref_UserPromo ON xref_UserPromo.userID = User.userID WHERE xref_UserPromo.userID IS NULL AND (xref_UserPromo.promID = 1 or xref_UserPromo.promID = 2)

Notice here the use of the left join. Also notice because you already know the promIDs, we really don't need to join the Promo table (we could if we wanted or we needed to look up the promIDs by name or something). The query looks a little ugly, but should run great AND provide you data you're looking for.

2700 rows is nothing for a good database engine. MySQL won't have any problems with it. I have a couple MySQL tables with over 3 million rows and I have many tables with over 100,000 rows. The key is writing smart queries which run fast and get ONLY the data you need. The other trick is keeping your data normalized (which is sort of what we are doing versus trying to shove all the xref table data into an array which gets stored in a table column).

Maybe think about it this way:

Lets say you do decide to store the xref table data as an array in a table column (bad - but lets use it as an example).

For each record, you have a fixed width field. Its data is comma delimited. The array would go into a VARCHAR field. The max length is 255 characters. What do you do if you exceed the 255 characters? You're stuck. Also, you've chewed up 255 bytes.

Now you use the xref table approach and have 2 integer fields. Each integer field requires 4 bytes of storage, so 8 bytes will be used to associate 1 person to 1 promo. At 2700 rows in that xref table, you're using about 21,600 bytes of disk space (doesn't take into account space needed for indexing). Play with some of the numbers and you'll find 2700 isn't a scarey row count at all.

holstead

Thanks so much! 5 star stuff.

If we ever meet, first round is on me 😉