Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Question Unanswered: Best Practices Question: Create many common databases or many tables in one database?

    General Best Practices Question: Create many common data databases or many tables in one database? I am not a DBA!

    In developing a web portal, ERP, or any large enterprise wide application made up of smaller task specific applications under the parent environment.

    Should the design be to create a common data database and a specific database for each app or just put create one database and use best practices naming conventions? RI may or may not be a concern.

    Areas of concern are: Maintenence, Security/Access, Performance, backup/restore, and file system and DB fragmentation benefits/hits.

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    It depends on a whole host of questions. Will you need to do reporting across these applications? How will security need to be handled? What is the estimated size and growth of these seperate applicaitons?
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Originally posted by rhigdon
    It depends on a whole host of questions. Will you need to do reporting across these applications? How will security need to be handled? What is the estimated size and growth of these seperate applicaitons?
    I know my question is a broad one and it does bring up a whole host of questions. I can see many pros and cons for both approaches.

    Reporting would be needed across all dbs or tables.
    Security would be ADSI/NT Auth Mode.
    Much of the apps data would grow very large (heavy I/O) and independently, while others would grow at a slower rate.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you mean by "many common databases"? Do you mean duplicate schemas? I'd advise against that, unless you want rolling out upgrades, synchronizing lookup tables, and consolidating data for reporting to be a full-time job.

    "Put all your eggs in one basket - and then WATCH THAT BASKET!"
    -Mark Twain

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Originally posted by blindman
    What do you mean by "many common databases"? Do you mean duplicate schemas? I'd advise against that, unless you want rolling out upgrades, synchronizing lookup tables, and consolidating data for reporting to be a full-time job.

    "Put all your eggs in one basket - and then WATCH THAT BASKET!"
    -Mark Twain

    blindman
    Databases that contain the data that is common to a specific application only and each then of those databases containing links to a database that contains data that is shared or common to all other apps/databases.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If they are truly separate applications, then they should be separate databases.

    Roll their data into a central data warehouse for analytical processing.

    Maintain common data in central database and publish it to the clients to keep them synchronized. Each application should be stand-alone, and not rely on links to other databases if it can be helped.

    This is going to be an ambitious project.

    blindman

Posting Permissions

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