hi im working on on a personal project and i need some help on how to structure my database and query.

so here is my problem and searching for a solution.
on my site everyday there will have a daily article or item grouped in categories. now a user has to subscribe to a category by entering his email and mobile number.so that every day when there is a new article or item in that category he will be alerted via email and sms.

so how can i structure my database to link the item with the subscriber email and number and so that only current day article get sent to them and not old one.sms will go via sms api gateway.


and how to query my db also i would need to make my sms query into a variable to send to my sms api.but how do i achieve that.

don't have to give the code just need a few guideline and pointers

thanks

please help me out any suggestion is welcome.

category(cat_id, cat_desc)
article(art_id,art_desc, cat_id)
user(userid, username,pwd)
user_category(userid, cat_id)
user_mail_log(log_id,userid,art_id,sent_time) optional

here when an article is added you can run query and loop it to send mail to user subscribed its category and save that log in user_mail_log table (optional).

to find out you need to join article, user, category and user_category table

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.