on 7/20/04 11:05 PM, Michael Stassen at [EMAIL PROTECTED] wrote: > What is user_id? You didn't mention it before. Will the user with > user_id=123 be able to make a 'work' group and the user with user_id=456 > also be able to make a 'work' group? That is, you have multiple lists > (group) for each user (user_id). If that's the case, we need to alter the > previous suggestion. You have to put the unique index on whatever > combination of columns determines uniqueness. It now sounds as if you want > each email address to appear no more than once per combination of user_id > and group. In that case, you'll need a unique index on those *3* columns:
Michael, I think you managed to grok what I am up to better than I manage to :-) Basically, this is a multi user mailing list manager that just so happens to be web based. I have a user table that contains data about the account holder, first name, last name, address, city, state, zip etc. This is where the user_id comes from, it is the PK that everything is held together by to keep John Doe's account separate from Jane Doe's. Your basic mysql multi user setup. I carry over this user_id into the addresses table, so, if John Doe is user id 123, a SELECT email_address from addresses where user_id = '123' finds just the email addresses for the mailing lists associated with John Doe's account. That select would of course find all "groups" for the user John Doe. I then have another table called "group_names" that is just a cosmetic name for each group, so a SELECT group_name, id, user_id FROM group_names where user_id = '123' would give me all the groups that the John Doe user has set up, something like: family 9000 123 friends 9008 123 mysqlbuds 9900 123 (the id is a unique auto inc) As I am sure you guessed by now the id in the "group_names" table is also in the addresses table, so my usual select to get a group of addresses for a user is something like: (note, I pass over the "group_name" id, sometimes I would JOIN if I needed the "cosmetic" name) SELECT email_address from addresses where user_id = '123' AND group_id = '9900'; This of course selects all the email addresses that are for user John Doe of the groups mysqlbuds. I have decided to drop the idea of merging groups that you touched on a solution for in your email. It adds too many problems, for example, to unsub a user fro a web form, if the John Doe has merged some groups after a mailing list message went out, there is a good chance I would not be able to find that user to unsubscribe them from that group. If John Doe wants to merge a group, he can simply delete the group and upload it to an existing group, in which case the dupes should be handled with the IGNORE option. This seems to solve that issue. So If I get you correct, and you seem to get me correct in your estimations, I do in fact need to have a unique index on 3 columns. The only way around that, would be to get rid of the "group_names" table and store the real group name along with the email address, which would mean every row would have a cosmetic name in it, replicated all over the place, and would be a less than efficient design. If all this makes sense to you and you have any other pointers, I am grateful, I learned a ton in this thread already today. The update duplicate is a awesome tool, adding more than one unique index is something I just was not even aware of. Thanks so much to you all, this is "logic" stuff that is really hard to scrub out of the docs, now that I have a idea of how to approach it, I can look up the syntax and be on my way, thanks again. -- ------------------------------------------------------------- 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]
