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
  • 269 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

A compact, practical approach that builds on 's idea: keep a clear separation of responsibilities (content, subscriptions, users, delivery records) and drive sending from a daily job that only selects articles whose publish/send date falls into “today” (account for timezones). Record every SMS/email send in a delivery log so old items never get re‑sent and so retries/failures can be tracked.

Keep these operational rules in mind:

  • Store timestamps in UTC and use a date-range query (start <= send_time < end) to pick "today" for the target audience timezone.
  • Normalize phone numbers (E.164) and enforce a verified opt‑in timestamp to meet carrier/compliance rules.
  • Use a unique constraint on (user_id, article_id, channel) in the delivery log to prevent duplicate inserts during retries or concurrent workers.
  • Throttle requests and implement retry/backoff for transient gateway errors; persist gateway message IDs for troubleshooting.

Example selection query (pick today's newly published items and only subscribers who haven't already got a delivery log entry):

SELECT u.id AS user_id, u.mobile, a.id AS article_id, a.title
FROM articles a
JOIN subscriptions s ON s.category_id = a.category_id
JOIN users u ON u.id = s.user_id
LEFT JOIN delivery_log dl ON dl.user_id = u.id AND dl.article_id = a.id AND dl.channel = 'sms'
WHERE a.publish_date >= CURDATE()
  AND a.publish_date < CURDATE() + INTERVAL 1 DAY
  AND dl.id IS NULL
  AND a.status = 'published';

Example send flow (pseudo-PHP): fetch rows, build a short templated message, call the SMS API over HTTPS, log success/failure into delivery_log with gateway_id and status, and honor rate limits by queuing work (cron -> queue -> worker) rather than doing all synchronous HTTP calls in a single process.

Operational checklist: test with a gateway sandbox, cap message length (or use concatenation handling), add unsubscribe handling, log gateway responses for audits, and monitor delivery rates so scaling is predictable.

Recommended Answers

All 2 Replies

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.