A Project also has "Project Tasks" which a project can have as many Tasks as you want. A Project Task has many of the same DB fields as a Project does with some other features like sort order.
Lastly a Project has "Project Documents" which allow you to upload images, text files, any sort of file really related to the project.
On the Project database, I have a field called "subscribers" which is a text field and I store a comma separated list of User ID's for user's who are subscribed to updates/emails related to this project. So the subscriber field is on a Per Project basis. One user might be subscribed to Project 1 but not Project 2.
The reasoning behind this Subscriber database field on the Projects, is I would like to send out a daily email to all subscribed users showing Projects they are subscribed to that have been edited the day before. So if I am subscribed to "Project 1" and it's database fields are modified, it has new documents added, or new tasks added, completed, or modified, then I would get an email the following morning showing all that data in a Daily Recap email.
My idea for this comes from using the popular Project Management app/service Basecamp which sends out a Daily Recap every morning of all projects and activity from the previous day that you are subscribed to.
I am looking for feedback on how to best implement this system. I have everything coded and working up to the subscription part where the user ID's are saved to the Project database. So I basically will have a cron or similar that runs daily to send the emails to everyone that is subscribed.
I guess I am looking for the best approach to determine what projects and such should be in these emails. On my Project Tasks database, I have a modified datetime field so I can determine when the items were modified, same on the Projects table...
So I am thinking I can just query my 3 database tables and build a list based on items modified in the past day.
What Basecamp does, is instead of sending a separate email to the same user for each project they are subscribed to, they get 1 email. To do this, I would have to iterate over my user list instead of my project list. On each User, I would then have to query all the projects to find which projects that user is subscribed to, then run my queries on the appropriate projects. Keep in mind, I can't simply store a list of projects each user is subscribed to on the user table as this is a module.
I am just hoping to get some feedback, ideas, suggestions on improvements to this idea or a better way or if what I have outlined sounds pretty good?