Member Avatar for eltonpiko

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

  • 2 Contributors
  • 2 Replies
  • 253 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for eltonpiko

please help me out any suggestion is welcome.

Member Avatar for urtrivedi

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.