I am stuck on how to design a set of tables. I hope someone can help me. I am using mysql 4.0.18-standard.
A little background, this is a mailing list manager, it support multiple account holders. User_id is the account holders id, this is unique auto inc and carried across all table to make sure I am only working on one particular account holders data. Email addresses can be "grouped" for organizational purposes only, so you could have groups of "friends", "family", "enemies". My trouble comes in with the dealing of unsubscribes and bounces. If I recipient ever wants to unsubscribe I want to mark them so across all groups, but I want to keep the email address on file marked that way so that they will never ever be emailed again. Mainly this is for bounces, if the email address bounces, it is a dead address, I don't ever want to deal with that address again. My first effort at this is with 2 tables, groups and addresses. If I want to get all addresses from a group for a particular account, I simply SELECT email_address from addresses where group = "family" and user_id = '123'; Inserting unique rows in this scenario is simple as well, but it allows for a email address to exist in more than one group at a time. In this scenario, when I add more addresses in batch, I use INSERT IGNORE, and since I have a unique index on the address and group, if the email address already exists in that group, it maintains the unsub status and happily ignores that insert, otherwise, it is not unique and it will insert it. This is flawed in it is now impossible to globally across all groups have a email address that can no longer be inserted and always marked as unsubscribed. I could update addresses set status = 0 where user_id = '123' which would in fact mark all those email addresses unsubscribed, but if a new group were created, a account holder could potentially get the unsubscribed address into the new group. One thought, would be on the creation of a new group, to select all the unsubscribed addresses into that group, but that gets messy and replication of data is starting to happen. I think I need to drop the "groups" idea somewhat. If every address was unique within a account holder/user_id, this is a solved problem, but I am at a loss as to how to still group the addresses both cosmetically as well as for the purposes of sending emails to to them. If the group_id was treated as a list of id's in a field, I think this would solve my problem, but I am not able to figure out if mysql has a field type to do what I want, the "set" does not seem to be it. If the email address was tied to groups with multiple id's in one field, this should work: User_id email_address group status 123 [EMAIL PROTECTED] 1,4,6,8 1 123 [EMAIL PROTECTED] 2,6,8,9 1 I think I can then sort out my groups with WHERE int in group. But this opens me up to one other issue, I have a upload system where new addresses are uploaded and imported in with the building up of insert statements. In short, if the email address does not exist, I need to insert it, that part is simlpe, the account holder chooses a group they want to upload into and it would get inserted as SET email_address = [EMAIL PROTECTED], group = '5', or whatever the group is. The trouble comes in the conditions that need to be thought of when a email address already exists, IF status = 0 ignore that address, we don't want to insert, IF status = 1, since it is already there, I only want to append/update the group id's to have the additional group set. Of course, there is chance the group id is the same due to account holder mis management of addressees, so that needs to be taken into account as well. The other problem is I only want to INSERT the addresses, I do not want to do any selecting ahead of time, this is too much overhead. Account holders will be sloppy, they will add new addresses to their internal address list, then they will submit the whole batch to me again, I need to only add the new ones, so these lists will get large, to select that many times is just too much. Any idea on how to best approach this, either with my ideas or a totally new method are appreciated. -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
