Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Question Should I create a new DB or use an existing DB?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    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!

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •