Richard,
I am wondering how you would set up relationship for databases with budgets for the coming years? I have used your resources to develop a good streamline process for now, but what do I do when next years budgets come out? Id like to run reports on the previous years information compared to the coming years. I currently have a BudgetT, PurchaseOrderDetailsT, and PurchaseOrderT as a baseline (with plenty of others), would you recommend creating a YearT and linking them together?

---

Reply from Richard Rost:

I recommend keeping everything in ONE TABLE and just use dates to track different years. If you want to use a single YEAR field with values like 2012, 2013, 2014 instead of actual date/time values, that's fine too... but using multiple tables for records in different years will cause trouble in the long run.

Sure... later on you can archive old information if you want to compact your databases a bit. But design your database to be able to work with multiple years from ONE table. I'd set up my budget tables something like this:

CategoryT: CategoryID, CatName
1, Utilities
2, Travel
3, Payroll
etc.

AnnualBudgetT: ID, Year, CategoryID, BudgetAmount
1, 2013, 1, 10000
2, 2013, 2, 3000
3, 2013, 3, 25000

So I've just allocated $10k for next years budget for utilities, $3k for travel, $25k for payroll.

ActualExpensesT: ID, DateTime, CategoryID, Amount, Description
1, 1/1/2013, 1, 350, Gas bill
2, 1/1/2013, 1, 425, Electric bill
3, 1/4/2013, 3, 750, Joe's Paycheck

See how this is coming together? Answer your question?

Oh, and if you want to get cheesy and use a YearT table just to store your year values: 2013, 2014, etc. you could do that. Just fill it with 100 years of info and you should be good.