internal mail multiple users DB table setup - help

[chidge]

So I am planning an internal mail application on my website. And I want this app to send to either a single user, multiple users or all users

I am using Javascript, Ajax and PHP and Mysql for this.

So ok 1-1 users is fine and multiple users are fine but I am a bit stuck on the best way to approach multiple users.

So to set the scene the user presses send on the write new email page. Then the information gets sent to the mysql Database. It is either the single persons id if they are sending to one person(1) or (all) if they are sending to everyone. or multiple users id's (1,3,4,9,2,6,13) if they are sending to multiple users.

So for the sentTo colum in my DB (the colum denoting who the email has been sent to) I will run it as say TEXT (need advise on this - it will be either a single integer (up to 4 characters), the text(all) or a comma seperated list and could be say 1000+ integers)

So ok thats the info in the DB. Now when a user goes to their inbox I am struggleing a little with the select statement to get their specific email from the email table

So select from email where sentto == $userid or sentto == 'all'

is fine but then to get there id from a multiple email is it best to use

select from email where sentto == $userid or sentto == 'all' or sentto like '$userid'

or somehow get the sento info from the db, explode it on comma (if there is a comma in there) and then loop through it until I find or dont find the $userid (this sounds very slow)

I hope that explains the problem a little

Any help is much appreciated on this matter and thank you in advance

[Old Pedant]

Nope, bad DB design.

You need a many to many table for your "sentTo".

DB design:

Table: Users
userid : int, probably auto_increment, PK
email : text
name : text
... etc. ...

Table: EMail
emailid : int, probably auto_increment, PK
sender : int, foreign key to Users(userid)
subject: text
message: text
... etc. ...

Table: EmailRecipients
userid : int, foreign key to Users(userid)
emailid : int, foreign key to EMail(emailid)


*NEVER NEVER NEVER* store a delimited list of key values (and, 98% of the time, of *any* values) in a single DB field. Period.

NOTE: You *might* want to special case "All" emails if you have a lot of them and/or a lot of users. You could just us a bit flag in the email for that.

[Old Pedant]

Actually, if you do *not* use a bit flag for "all" and instead go ahead and put all those entries into the EmailRecipients table, then you could add one (or more) field(s) to that table:

Table: EmailRecipients
userid : int, foreign key to Users(userid)
emailid : int, foreign key to EMail(emailid)
received: boolean not null default false;

So, now, when a user reads the email, you mark it as received. And when he/she deletes it, you simply remove the record from EMailRecipients (or maybe not...up to you...maybe the sender would like to know who has/has not read the sent mail.)