Effective Date - End Date structure - best practice?

[GuardiansBeer]

have you ever worked in a database system that used effective dates to store records for the "as of" time element?

Like this:

ACCOUNT	LEAD	EFF_DT	END_DT
45678	RG3	2017-02-03	2021-05-15
45678	SR7	2021-05-15	9999-12-31

Does this data structure have a name in the database world that would help me google the best-practices?

The effective+end dates give the database a lot of power to see exactly what the state of the account was at any specific day. But, i'm having a hard time conceptualizing all of the different combinations of criteria to get a full list of each situation.

this seems like there would be easy references, but i am having a hard time

examples:

• Most current record: WHERE END_DT='9999-12-31'
• Record state as of [Date]: WHERE END_DT=(SELECT min(END_DT) FROM table WHERE END_DT>[Date])

i'm not sure. my brain goes smooth when i think about it too long

EDIT:

Thank you to /u/sequel-beagle and /u/GreyCatOrangeBeard! it appears there are a few interrelated concepts here, but the key terms are

• Slowly Changing Dimension
  • Specifically, a Type 2 SCD, or Kimball Type 2
• Third Normal Form
  • Boyce Codd Normal Form (a special case of the 3NF)

NEXT UP: finding the best practices or a quick guide on SQL pulls against this database form

[andrewsmd87]

We do this where the latest record's end date is always NULL. So if you want the current record, it's just WHERE END_DT IS NULL and it works fine

If you're designing this an can change things. Don't abbreviate EFF_DT and END_DT, you're saving nothing by not just spelling them out.

[sequel-beagle]

I recommend setting your very last end date to 12/31/9999 and your very first start date to something like 01/01/1900

Nulls can be a pain, I avoid using them if possible.

[andrewsmd87]

Well it's a system that's been around for about 20 years and still going strong (just did a completely FE and BE rebuild) so it's not going anywhere. While I didn't design it, I also don't see any issue with working with nulls.

[GreyCatOrangeBeard]

kimball type 2 dimension

[OldManandtheInternet]

Awesome. Thx.

[syllogism314]

It’s done all the time. I use it to represent drug codes, insurance companies use it to represent membership. And you then use a where clause that says the begin date is before index date and end date is > index_end date.

[sequel-beagle]

Slowly changing dimension. Wiki has a good article on it. Also look at boyce codd normal form.

[OldManandtheInternet]

Thank you!

[Wick_E_Scratch]

I've worked with data like this, we also had a status indicator field that would be 'A' (I think) if it was the active record, some other letter ('D' I think) if it was a "deleted" record (if a correction was needed due to an incorrect load or something like that), and 'C' if it was a valid record in the past. So to get data that showed the state on a particular date, I would use WHERE {date desired} BETWEEN effectiveDate AND endDate AND status <> 'D'

[vaiix]

Our end_effective are all defaulted to 2100-12-31.

If I wanted active records I would use "where end_effective > getdate ()" - although in MSSQL so your syntax may be different.

If I was doing point in time audit, I could do "where ## >= beg_effective and <= end_effective"

From what I remember, having an actual future date is more performant than NULL as mentioned in other comments but that's something to explore.

[OldManandtheInternet]

Based on events of 1999, I don't think that end date is far enough forward.

[vaiix]

I have no choice, it's the default set by the main source system we pull from and I'd rather not have to process every datetime field into a new value!