Fiscal year handling strategies in database design

By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years.

What way of implementing this would you prefer, and why:

1. Separate fiscal year data based on multiple separate database instances (for example, on every fiscal year start you could create a new instance with no d开发者_StackOverflowata)
2. Have everything in one database, but with logic that automatically separates records from different years.

Personally, I have "seen" both methods, and I would choose the second. The only argument I can think of for the first method is to have less records in case that these are really big databases - but still, you could "archive" old records by joining them in summaries or by some other way. What do you think?

Separate fiscal year data based on multiple separate database instances (for example, on every fiscal year start you could create a new instance with no data)

No. Do not create a separate database instance, database, or table per fiscal year.

Besides not being normalized, you would be unnecessarily duplicating the supporting infrastructure: constraints, triggers, stored procedures & functions would all have to be updated to work with the new, current fiscal year. Which would also complicate data for future years for budgetting and planning.

Have everything in one database, but with logic that automatically separates records from different years.

There's no need for separation, just make sure that records contain a timestamp, which can then be used to determine what fiscal year it took place in.

There is a third alternative.

Create a table, let's call it "Almanac", that has one row per day, keyed by date. In that table you can have a whole lot of attributes that are determined by the date. Among them could be some attributes for which there is a function, like the day of the week. Some attributes could be company specific, like whether or not the day is a workday at the company.

Among the attributes could be the fiscal year, the fiscal quarter, and the fiscal month, if your company has such things. It's not particularly important to normalize this table.

Write a program that populates this table. All the convoluted logic that goes into calculating the fiscal year from the date can thus be in one place, instead of scattered through out your system. Ten years worth of dates is only going to be about 3,650 rows, a tiny table by today's standards.

Then, cutting all of your date driven data by fiscal year, fiscal quarter, or whatever is just a matter of joining and grouping. You can even automate the production of different time frame views of the same data.

I've done this and it works. It's especially good in reporting databases and data warehouses.

No need for duplication. A time-stamp may be good enough, but to borrow from data-warehousing, you could create a "date dimension". It is a table with a row per a day and a column per date attribute. Some of those columns may be fiscal year, fiscal quarter etc. Then you add a DateKey to the transactions table and join the date dimension when querying.

Something like:

select sum(t.Total)
from Transactions as t
join dimDate as d on d.DateKey = t.DateKey
where d.FiscalYearQuarter = 'F2009-Q3';

The date dimension table may look something like:

CREATE TABLE dimDate
  ( 
   DateKey int                      -- 20090814
  ,FullDate date                    -- 2009-8-14
  ,FullDateDescription varchar(50)  -- Friday August 14, 2009
  ,SQLDateStamp varchar(10)         -- 2009-08-14
  ,DayOfWeek varchar(10)            -- Friday
  ,DayNumberInWeek int              -- 6
  ,DayNumberInMonth int             -- 14
  ,DayNumberInYear int              -- 226

  -- many more here

  ,FiscalYear int                   -- 2009
  ,FiscalQuarter char(3)            -- FQ3
  ,FiscalHalf char(3)               -- FH2
  ,FiscalYearQuarter varchar(8)     -- F2009-Q3
  ,FiscalYearHalf varchar(8)        -- F2009-H2 
  );

You would pre-load the dimDate, from way back in past to forward in future; 100 years requires 36.5k rows -- not much for any DB.

Each entity should have its fiscal year as part of the metadata/staticdata.

From that you can easily handle the fiscal year breaks, and typically Databases can handle VERY LARGE amounts of data, so you should not have a problem.

Using the correct indexing will greatly inprove the performance of your queries, so worry about the performance once you hit the snag. Until then, worry about the code