How to create database records for a financial year

I need to create entities (specifically contracts) in a database table that are associated with a financial year. These contracts will be applied to projects. Any contract variation will be recorded by creating a new contract record for the same financial year but the original will remain associated with the project as a history. The projects can last several years and so at any one time a project will have a live contract record for each year as well as any number of historic contracts for that year.

All of which is inci开发者_StackOverflowdental but I'm trying to provide some context. If the contracts where for the year - it would be easy and I'd just store the Year either as a date field with the 1st of January or just an Integer. However the contracts run for financial years and I don't know how to approach this. I don't want a separate table containing the financial years as I don't want the users to have to maintain this. I don't want to store the financial year as a string "2009/2010" as this is not ideal for sorting/extracting the data.

Any ideas will be helpful, my best so far is to have starting and ending year in 2 columns and just "KNOW" that starting is April of the year etc

If you don't have different types of financial years (e.g. US starts Jan, UK starts Apr) then I would simply use an integer and adopt a standard. e.g. Fin_Year = 2010 implies Financial Year Ending Mar 2010 (2009/2010). This would not need an extra table or storage, the meaning is implicit and your code should understand that Fin_Year is a special kind of date.

EDIT: UK Government data standard uses ending year to represent a particular financial year.

+1 If storing the financial year is necessary, then I agree with Joel.

However, another approach you could consider is storing only the date from which each contract takes effect (which in fact you might have already intended to do). Hence, the database schema itself doesn't care about financial years, but you can put this logic into your queries. To find the current and historical contracts for the current financial year:

SELECT * FROM Contracts
    WHERE StartingDate >= '20090401' AND StartingDate < '20100401'
    ORDER BY StartingDate DESC