views:

66

answers:

2

Hello,

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 incidental 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

Thanks

+2  A: 

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.

Joel Goodwin
With that link as well I wish I could vote you up twice Joel! My issue with this solution is the implied knowledge that the single year field covers the years - who ever comes after me would need to know that (yeah I know comments help etc but I try to make my code very simple and obvious) - but as I work in Local Government the link you've added validates that implicit meaning. Fantastic cheers Joel.
David A Gibson
A: 

+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
Todd Owen
If there are lots of joins based on FY, then this is likely to be prohibitive. On the other hand, if there's a lot of date based queries, this might be the way to go.
Joel Goodwin
Hi Todd, the reports will be by financial year with a possible interfaces to the Finance system but I agree in principal with what your saying. Cheers
David A Gibson