Wednesday, September 9, 2015

Defining a monthly period for an application

Sometimes it happens when by application's business logic you have to define a period, which is associated to a particular month.

Currently I see at least three possible solutions of implementing it:

Solution 1: Use first day of the month. 

That solution is simplest one. You just use a DATE data type to identify a period and use for it the first day of a given month.
- Simple to implement, use only one column with simple basic data type;
- DATE data type uses only 3 bytes;
- That is possible to have in that column value other than first day of the month. If that happen your search criteria or joins won't work correctly;

Solution 2: SMALLINT data type for monthly period identification.

If your application starts it's periods after year 2000 - 21-st Century gives you unique opportunity starting making new mistakes from scratch. As before the "Problem-2000" we can start using just two or even three digits to identify a year and two digits to identify a month. For instance September of 2015 can be coded as number "1509". Because SMALLINT max limit is 32K your application's periods should be OK until end of December of 2327.
- Only 2 bytes to store the period value, which is good for huge data sets and multi-joins;
- Simple reading form;
- People acceptance barrier. Some people can't easily switch from form of "20150901" to simple "1509";
- Necessity to transform the value to a DATE format for most of the reporting. For that purpose to get date in the format of "YYYYMMDD" you can create computed columns with formula like: "CAST(2000 + Period/100 + Period %100 as VARCHAR)+'01'";
- That is possible to insert period value for a month with number bigger than 12. To prevent this you have to add check constraint with a check like: "(Period %100 <= 12)";
- Partial inability to represent periods before year of 2000. Sure you can use negative values, but it will loose a benefit of being simple.

Solution 3: Use reference table.

That is a heavy weight solution. You create a table where you specify your periods. Here is a possible table design:
- You have very informative reference table, which you can possibly use for any kind of filtering or calculations;
- You can supply number of individual attributes to list of your periods;
- Potentially you can switch in the future to different length of a periods and be completely move away from "Monthly" periods;
- Reference table requires at least minimal maintenance;
- Period ID is not informative and you have to always join "Period" table to get a date.


There can be also other solutions to keep monthly periods along with their combinations.
For instance my favorite one would be combination of #2 & #3.
With adding Foreign Key check constraint gives you full benefits of both solutions with mitigation of their weaknesses.

Then sample data would look like this:

No comments:

Post a Comment