I'm trying to get a feel here from some other database professionals... what criteria would you use to decide whether or not a new system of tables should have its own database or be included in an existing database?
Our company database has about 40 tables which load daily from another extract environment. New requirements dictate that I need to load and retain a subset of 13 of these tables at a few different time intervals.
I could create the new schema in 1 of 2 ways...
1) add the new tables to the existing company database with time suffixes... as in :
2) create two new databases named EARLY_REPORT_DB_6AM, EARLY_REPORT_DB_8AM, as in...
Other Notes :
- Regular users need access to the original tables only.
- A special report process will run using the 6AM DB & another special report process will run using the 8AM DB.
- By definition, these tables will never intersect (that is, we will never need to access tbl_Customer_6AM & tbl_Employee_8AM in the same query).
Would you include the new tables in the current database, or would you create two new databases. What would be the pros & cons of each?
Couldn't you build one table with flags as r937 suggested, and add views for the three groups?
Remember, your PK and FKs can consist of multiple fields.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert