CODEWITHSUNDEEP
sqlsql-serverdatabase
nejc.m
nejc.m

Reputation: 300

Email logging database table structure

Hei. I would like to hear if anybody already built something similar already and how did you approach and structure this.

So I would like to build a log in the database of all emails that are being sent out from our system. The emails are connected to different objects (products/listings, bookings, etc.). So the log is needed because if we are sending out a sequence of emails we need to know which one was sent out last and when.

The question is how to structure the logging tables to be able later query all/last/specific emails that were sent out for a specific booking for example. Is it better to keep a log table for each object or one generic table for all (maybe with a connection table).

I was looking online at some approaches how to structure tables for logging but would like to hear some opinions on this specific matter.

Thanks for all the help!

Upvotes: 0

Views: 1831

Answers (1)

DancingFool
DancingFool

Reputation: 1267

We have a table for a similar purpose. It contains things like

  • ID
  • LogDate
  • UserID
  • TypeID (we store emails, SMS and other actions in the one table)

Links to the source of the action. We have a large number of sources that can do actions logged here - Customer docs (various types), supplier docs (Various types), reports, tools etc. Mostly we don't need to join back to the document in SQL, just have an ID and source type, so we don't store them as separate columns for each type, just type and ID. By keeping this, we can open the source document from a grid of these logs, where applicable.

  • SourceTypeID
  • SourceID

Links to commonly searched/selected parent tables. We kept these linked directly because we often do join on these in SQL for various reasons, and also to help keep referential integrity if the parents are ever merged. (Documents referenced in SourceID above are never merged). Any log from a customer or purchases doc will have one or more of these, logs from internal tools may have none.

  • AccountID
  • SupplierID
  • CustomerID (Customer is not the same as account - think delivery address as opposed to who pays)

Message data details. We don't need to store the email body or attachments for our purpose (some people do). We do store the email subject line (or whole SMS message).

  • EmailAddress
  • EmailBCC
  • SMSAddress (phone number)
  • Subject (or SMS message)

This table is indexed on the commonly filtered columns - AccountID, CustomerID, SupplierID, UserID, SourceID. From any document we can quickly find all emails/sms etc about the document by sourceId (with sourceTypeID filter). For any account/supplier etc we can quickly find all messages from any document for that account.

Upvotes: 1

Related Questions