1

I am planning software which includes a relational database of people. For each person, data includes such things as current employer, job title and phone number. Since these are things which may change over time, I want to be able to tell the difference between a fresh record (more likely to be correct) and an old record (less likely to be correct).

I could, of course, add a "verified on" data field, but that implies that every item has been verified, and that's not necessarily the case. For example, if a phone call is made and connects to the right person, we could take that as implicit verification only of the phone number, and not other items such as email or even employer (the company could have been purchased/renamed for instance).

So now I'm thinking that perhaps a better approach would be to have an additional table which has four items: the record ID, a field ID, a date and a boolean result (i.e. TRUE meaning the indicated datum is correct and FALSE indicating it is not, but perhaps the correct value is unknown).

Are there better ways to approach this?

7
  • 1
    It sounds like you are trying to incorporate aspects of a workflow into the domain object itself. You might consider those as two separate things. The workflow is probably more likely to change the domain object itself. Depending how the data is used, you may want the domain table to only contain verified data, and store the unverified fields in a workflow table of some kind. Think of it as a form that you fill out in order to request an update of the permanent record.
    – John Wu
    Jan 23 '21 at 19:36
  • If the domain model is critically interested in when a contact detail was verified, it might make more sense to blow the table apart into records containing both specific contact information, and when/if it was last verified.
    – Kain0_0
    Jan 25 '21 at 5:56
  • Based on the problem you're posting, can I infer that your system only allows for one of each contact detail per person? Because if you assume the possibility of multiple (which e.g. for employer seems quite relevant), then that data lives in a table (and thus row) of its own, giving each their own verification date. I can't judge your entire architecture, but it may very well be easier to just move to such a database schema.
    – Flater
    Jan 25 '21 at 12:32
  • @JohnWu: I think the issue is not so much verified vs not verified, but rather a spectrum of when something was verified. There's no innate cutoff for something to be considered "not verified" anymore, it's more a matter of always favoring recent data (OP, correct me if I'm wrong). Your suggestion might be aimed at the wrong question here.
    – Flater
    Jan 25 '21 at 12:48
  • @Flater, you're correct: verified is not a boolean value in this conception, rather it's a timestamp. We might verify a phone number today and it could change tomorrow; or a phone number unverified for a decade might still be correct.
    – Edward
    Jan 25 '21 at 13:05
1

There are two ways that I can think of. The goal is the same in either case: elegantly bundling a piece of data with a verification date.

Option 1 - Separate tables

You touched on a similar solution, but I would suggest moving to a structure with multiple extra tables. In your case, given the examples, Person should have a one-to-many reference to PhoneNumbers, EmailAddresses, Employers.

I would suspect that since your interest is to track these people, that you'd generally already be interested in tracking any changes to their details/employment; so you might naturally already have evolved towards using a one-to-many setup here to track the detailed data.

And when you get those tables, simple add a VerifiedOn field to all of them. This way, each separate phone number / email address / employer has their own verification date.

Option 2 - Value objects

This is based on my experience with Entity Framework, which makes this significantly easier, but it can be done without EF.

Rather than defining a string property on your person, define a custom data class. Something along the lines of:

public class VerifiedBase
{
    public DateTime? VerifiedOn { get; set; }
}

public class VerifiedString : VerifiedBase
{
    public string Value { get; set; }
}

public class VerifiedEmployer : VerifiedBase
{
    public string Name { get; set; }
    public string Address { get; set; }
    public string TaxCode { get; set; }
}

public class Person
{
    public VerifiedString Email { get; set; }
    public VerifiedString Phone { get; set; }
    public VerifiedEmployer Employer { get; set; }
}

As I mentioned, EF makes this fairly easy to implement. It can figure out that it needs to add multiple columns to your Person table and will automatically map those columns to the properties of the value object. More information for EF value objects here.

But you can implement the same approach without using an ORM, you'll just need to map the columns appropriately.

However, as you can see with my VerifiedEmployer example, value object can be abused to fill the role of something than an extra table should really be fulfilling. I suggest favoring option 1 unless the wrapped data is provably trivial, not indexed and not shared between different Person entries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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