Archiving Store Records

[jay0316]

Our sales reps record store information about products etc using forms that put the data into a table in phpmyadmin.  Each store has its own record in the table.  At the end of the year we would like to clear out the values, but keep the previous record in case we would want to use it later down the road to pull info on a store in a particular year.

 

I was wondering if it would be better to just copy that table and give it a year (ex. "dealers_2009" ).  Each year we'd be adding another table.  Or would it be better to set up another table (ex. "dealer_archive") with a year column in it and transfer the records to that table each year.  Obviously the easiest option would be the first, but I want to do the best option.

 

Any recommendations?

 

[ignace]

If you have the possibility create a new database with all the tables of your current database and copy the data to it. This is usefull because now you can keep using your current application (as the table names remain the same). Give your database account only read access to this archive database (so no data can be changed). And through a dropdown you can then switch between the archive and your active data.

 

If all data has been successfully transfered perform a truncate on all tables.

 

If you don't have the option to create a new database through account limitation or hard-drive space I suggest you use something like outfile(1) or perform a sql dump(2)

 

1. http://dev.mysql.com/doc/refman/5.0/en/select.html

2. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

[jay0316]

Thanks for the quick response.  I like where you are going with being able to use the same app to view both tables.  We have the server in house and have the space and privelages to do what you're saying.

 

Are you saying to create a table with the same name, but have a column for the year and put it in a seperate database?

 

And then the drop down would allow them to select the year they want to view?

 

[ignace]

Are you saying to create a table with the same name, but have a column for the year and put it in a seperate database?

 

No I'm saying you create a new database, add the table structure(1) and copy all data from your current database to your newly created database.

 

database (<- working database)

database_archive_2009 (<- archive database, user-account(2) has read-only access)

database_archive_2008 (<- archive database, user-account(2) has read-only access)

 

Each database will share the same table names

 

database

-table1

database_archive_2009

-table1

 

Now if you add a dropdown to your application and query mysql (or some other engine) for all databases for your current user-account it will return:

 

database

database_archive_2009

database_archive_2008

 

You add these to your dropdown and when the user clicks submit you use something like:

 

mysql_select_db($db/* $db = $_POST['db'] and validation */);

 

All the rest of your application will remain the same. You may also add some constant or something to indicate that this is an archived version (grey out edit buttons).

 

1. table structure:

CREATE TABLE .. (

id integer not null auto_increment,

..

 

2. user-account: the account you use to connect to your database

mysql_connect(.., user-account, ..);

[jay0316]

Thanks for clearing that up ignace!

[ignace]

Good luck