I have a pretty elaborate question here, about how to have yearly versions of data retreival from mysql, as a PHP application.
I'm going to have a year drop down on my main application. What I'm wanting to know is what the best approach to display data based on the current year, and then offer old data for previous years.
For example, let's say, year 2009. At the end of the 2009 year, I decide to go through my customer accounts, and I know know that 10 of my customers are no longer going to be a customer, however I also know that I'll get new business in the coming year. How would I be able to then make these changes, and then move onto the next year, for instance 2010, without those customer, but be able to go back to that year and edit information (such as if they have an outstanding balance, and I'm just updating information related to that year)?
Another example is (keeping in mind of the previous information), I add a new customer in 2010; I only want that to show for 2010 (and coming years, if that customer decides to stay as a customer). I do not want the "cancelled" customer from the previous year to be in 2010, and I do not want the new 2010 customer to be in the previous year. So, basically, using one database, how can I have multiple "versions" or "years" implemented into one database.
This is more of a technical AND dba design question. I already have the database structure and everything, but I'll probably need to update the schema after discussing this, as I'm sure that the database structure might need a little bit of changing after this.
Please let me know