mardi 21 novembre 2017

Handling reference data which have a valid period of when they can be used within the software?

I have reference data that can be used by the application between certain dates, past these dates the value can no longer be used for current data but can be used for historical data.

For example, I have an entry form in the application that can be used to enter in details of perks that staff are receiving and the date range of when they're receiving these perks. These perks are only valid between certain dates (or until otherwise specified by the company). So the list of perks could look like this:

  • PerkA (valid between 01/01/15 - 31/12/16)
  • PerkB (valid from 01/01/15)
  • PerkC (valid between 01/01/17 - 31/12/17)

The staff entry form would include a table with the following:

  • Perk (with options from above list)
  • Perk start date (cannot be null)
  • Perk end date (can be null)

So in the entry form if I'm entering in staff details on today's date (21/11/17), then only PerkB and PerkC are listed as available options. If I'm retrospectively modifying a record that was added on 30/09/16, then PerkA and PerkB should be listed as options.

To store the reference data, my intention is to have a table with the following fields:

  • PerkName (not null)
  • PerkStartDate (not null)
  • PerkEndDate (nullable)

The current reference table has the following:

  • PerkName (not null)
  • PerkActiveStatus (not null, boolean to indicate whether value can be used)

The current design means it's very limited in terms of handling historical data and we have not hit issues until now because the perks have not had "expiry dates". Would the approach using start and end dates be the best way to handle this or is there another way to consider?

Aucun commentaire:

Enregistrer un commentaire