I've redesigned some tables (small in terms of data and number of rows) which some existing applications are still using and would like to know the best practice as keeping both the old and new tables. Basically, a new application will be developed to use the new tables while existing applications will continue to use the old ones. Eventually, the existing applications will be converted over to use the new tables. What is the best way to keep both old and new tables? i.e. create a new schema and add the new tables, use the existing schema and add the new tables (with different names), others?
Name the new tables by the new name of your choice, and when the time comes, rename the old tables to some bogus name (or drop them) and create a VIEW for each table, pointing to the new tables, which bear the same names as the old table names.
I guess I was trying to focus on a "seam-less" transition for the old applications, when it came time to point them at the new tables.
If you're going to have to do a re-design on the old applications anyway, then table placement (new schema / old schema) will have to do more with your shop standards than anything else. Probably wouldn't hurt to keep these tables in the same schema IF there are other tables that will not change during this transition. If all tables are going to change, then I'd go for a new schema.