I have to create organizations (particularly contracts) inside a database table which are connected having a financial year. These contracts will be relevant to projects. Any contract variation is going to be recorded by developing a new contract record for the similar financial year however the original will stay connected using the project like a history. The projects lasts many years and thus at anyone time a task may have an active contract record for every year in addition to a variety of historic contracts for your year.

Which is incidental but I am attempting to provide some context. When the contracts where for that year - it might be simple and easy , I'd just keep Year either like a date area using the first of The month of january or simply an Integer. Nevertheless the contracts run for financial many I'm not sure how to overcome this. I'm not going another table that contains the financial years as I'm not going the customers to need to maintain this. I'd rather not keep financial year like a string "2009/2010" because this is not well suited for sorting/removing the information.

Any ideas is going to be useful, my best to date would be to have beginning and ending year by 50 percent posts and merely "KNOW" that beginning is April of the season etc


Without having various kinds of financial years (e.g. US begins Jan, United kingdom begins Apr) i quickly would simply employ an integer and adopt a typical. e.g. Fin_Year = 2010 suggests Financial Year Ending Marly 2010 (2009/2010). This could not require an additional table or storage, this is is implicit as well as your code should realize that Fin_Year is really a special type of date.

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

+1 If storing the financial year is essential, then To be sure with Joel.

However, another approach you could look at is storing just the date that each contract becomes effective (which actually you may have already meant to do). Hence, the database schema itself does not worry about financial years, but place the this logic to your queries. To obtain the current and historic contracts for that current financial year:

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