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 :
tbl_Customer
tbl_Customer_6AM
tbl_Customer_8AM
tbl_Employee
tbl_Employee_6AM
tbl_Employee_8AM
tbl_Plan
tbl_Plan_6AM
tbl_Plan_8AM
...etc

OR

2) create two new databases named EARLY_REPORT_DB_6AM, EARLY_REPORT_DB_8AM, as in...

COMPANYDB
tbl_Customer
tbl_Employee
tbl_Plan
...etc

EARLY_REPORT_DB_6AM
tbl_Customer
tbl_Employee
tbl_Plan
...etc

EARLY_REPORT_DB_8AM
tbl_Customer
tbl_Employee
tbl_Plan
...etc


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?

Thank you all,
Leon

i would create one new table (not two) and i would flag the rows as they are being inserted as either 6am or 8am

whether this table is in the same database or a new database doesn't matter

That's a good suggestion.

Unfortunately, I can't do it that way because the tables could have a different set of relationships defined on them and it would require too much of an overhaul in SP code.

What's most important for me, though, is to get a feel for what criteria people use for creating a new database.

Thanks!

Different relationships?

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.