I am working on a new thing. It's like a table with a lot of companies and one table with a lot of users.
The users can write a little newsletter and my page will send it to the companies in the company table that are in the chosen type of business.
When the user is writing the text a table called send_tbl will be used. This is where all the users newsletters are stored with all the info for the newsletter.
When everything is done, the picked companies will be in a new table and set as a cron job sending out a bunch of newsletter every 10 mins or so.
I will also make a table where I store a list of companies that unsubscribe.
A company can unsubscribe to all newsletters, then it's set to 1 in the unsub field in the company table.
But, they can also unsub from a specific user only. So, if they unsub from a newsletter that is being sent, I thought about building a separate table containing all the single unsubscriptions. So, if a company is clicking the "unsub from this user" button in a newsletter from user 1240, then I will add comp_id and user_id in a table called unsub_tbl.
The first table of the companies contains | comp_id | comp_name | comp_email | comp_type | comp_unsub | and so on...
The send_tbl contains | send_id | user_id | news_text | and so on...
And finally the usnsub_tbl contains | user_id | comp_id |
So, when a newsletter is being sent it has to check wether the type of company is what the user picked. It also has to check that the company haven't unsubscribed from all newsletters in my db.
Then it has to check if the company haven't unsubscribed from only this user (in the unsub_tbl).
And when these things are checked, then they should be added to a new table called pipeline_tbl which contains |send_id | comp_id | and so on... and this table is the one I will pick the parts that will be sent using a cron job.
Is this a good way to do it. And how would a query look to check these settings so it runs through the unsubscription parts the correct way.
This is all in my head only right now, but I don't really think I know if it will work and how to make it work this way.
So, any thought or ideas or queries are welcome.