4

We need to email our customers once per year (yes it's opt-in etc).

As I see it we have 2 ways to record the fact we contacted them (so we know not to contact them for another year). A log, or a 'lastContacted' field in the customers table.

I don't like adding fields to the customers table - however, if we go the log route (inserting a record every time a customer is contacted into a log table) - then have to look that up every time we want to see if a customer has been contacted - that will turn into, I assume, a much heavier query over time than looking up a single lastContacted field in the Customers table.

Also, for me logs feel like something that can be periodically cleared at some point in the future (a decision perhaps made by another developer not realising the impact).

Should logs be used for such things? What is best practice here?

8
  • "I don't like adding fields to the customers table" What about a new table with a FK pointing back to your Customer Id on the Customer table? That way it doesn't pollute the Customer table but you still have a quick lookup for the 'lastContacted' of and customer? I wouldn't personally recommend reading logs in your application code unless absolutely necessary.
    – neilsimp1
    Commented Jun 27, 2018 at 11:53
  • That's certainly an option - though it does add a bit of complexity. Maybe just adding a field to the customers table is the best approach then? If there were - say - 20 values here, I think your idea would make more sense, but it's just a dateTime
    – niico
    Commented Jun 27, 2018 at 12:00
  • 2
    Fair enough, a single column may make more sense, then. Either way, database gets my vote over a log file if you need to look it up.
    – neilsimp1
    Commented Jun 27, 2018 at 12:11
  • To clarify - it's a log in a database table, not in a separate file. The log just records the fact an email was sent to a customer - there are just a few hundred of these per day, the data recorded is minimal.
    – niico
    Commented Jun 27, 2018 at 12:13
  • 1
    Then do what "cron" interpreters do. Calc the date of the "next" communication after the current notification. Schedule the notification to that day and wait...
    – Laiv
    Commented Jun 27, 2018 at 12:55

2 Answers 2

3

I'd use a separate table for customer interactions, communications, and "touch points" (including mailings and other marketing). Customer communication data is generally stored in mainstream CRMs as a distinct business entity.

In DynamicsCRM, for example, the "superclass" you're looking for is called an Activity:

Activities are used to record a specific type of communication, as well as the subject, time and details of that communication. Activities are actions such as emails, phone calls, meetings, etc. Using activities helps you and your organization understand all of the communication your organization has had with each customer or prospective customer.

... and each type of interaction (Email, Phone call, Meeting) is it's own "sub-entity" of "Activity." etc.

If you're building custom software to "manage customer relations," I'd highly suggest getting a trial of a well-established CRM (like Dynamics or SugarCRM) to see how these systems model customer data.

1

Also, for me logs feel like something that can be periodically cleared at some point in the future (a decision perhaps made by another developer not realising the impact).

This sounds like you have an existing log table in mind which has already a different purpose which is of minor importance for the business (otherwise noone would probably come to the conclusion of intentionally deleting data from it). If that is what you are talking of, I recommend heavily

Don't mix different purposes in one table!

That bears a high risk of getting these purposes into conflict, as you already mentioned by yourself. So this rules out to use an existing log table, and gives you about 3 different options:

  1. use an additional column lastContacted in the Customer table

  2. use another table, let's call it Contact, in a 1:1 relationship to Customer

  3. use the same Contact table in a 1:n relationship

If your business requires not to store just the last contact date, but a history of contacts (which is what your log table proposal would include), then it is clear you need option 3. If that is not the case, and your business requires just one last contact date, YAGNI recommends to go with option 1. Only if you have a real technical reason (and not some superstitious gut feeling) against option 1, though only one last contact date is required, go with option 2. Option 2 will also be fine if a Contact will become an entity with several attributes, not just a date, and you want to use it as an abstraction on its own.

Note also, performance is probably very irrelevant here, recording contact dates once a year per customer in a separate should not bring you into trouble, at least not if your database is properly indexed.

4
  • Thanks. There will be a log of sent emails in a table. The question is whether that log should also be used to find the most recently contacted date, or whether it's best to store that in a new field. Regarding "hopefully get fired" sure in a perfect world devs wont make mistakes - but we don't live in one. Making it less likely developers will screw up is a good aim IMHO.
    – niico
    Commented Jun 27, 2018 at 13:37
  • @niico: I rewrote my answer. Better now?
    – Doc Brown
    Commented Jun 27, 2018 at 18:37
  • Why don't just schedule the next notification and forget any "last contact date"? Why evaluate over and over whether new notifications must be sent?
    – Laiv
    Commented Jun 28, 2018 at 6:39
  • @Laiv: I think OP did not explain the actual use case well, so we don't know how exactly they intend to use the last contact information and if it leads to some "evaluation over and over again". So as I wrote, OP needs to clarify and analyse business needs first, I am very much under the impression they try to make a domain decision by technical measures instead of making a precise requirements analysis.
    – Doc Brown
    Commented Jun 28, 2018 at 6:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.