SQL database management question

[FiveFlat]

I created a database to keep track of expenses for tax purposes. Now it's 2004 and I don't think I want to keep writing rows to the same table.
Should I create a new table for 2004? or just backup the 2003 table and delete all data from it?

If I create a new table for it, I'm going to run into problems with queries if I have several 'expenses' tables with different names won't I?
Any suggestions would be appreciated.

[bastien]

hey five,

couple of ways to approach this.

1. add a field for the year and then change the reporting queries to accept another parameter of 2004

2. export the data to a flat file and store it, then delete the old info from the table

3. rename the current table and then create a new table with the same structure

In pure db terms, option 1 is the correct method, but it doesn't really matter.

2 is probably the best option, and involves no table changes

[FiveFlat]

Thanks. I talked with a close friend about it and he thoughts were that you should NEVER store like data in different tables.
His suggestion was to store all expenses in one table and then just query by year. And archive each year as nessecary.
Allthough my 2003 expenses total only 22KB I should be fine with the 7 year IRS rule and 2GB mysql table. 😉