Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DB Design Dilemma!! One DB or Many DBs??

Re: DB Design Dilemma!! One DB or Many DBs??

From: Jerry Gitomer <jgitomer_at_erols.com>
Date: Mon, 11 Feb 2002 23:57:39 -0500
Message-ID: <3C68A0C3.7F42B847@erols.com>


Rob Hindman wrote:
>
> Hi I have a major DB Design Dilemma !!
>
> I am a DBA working with a group of engineers on a new software system,
> which will use DB2 version 7.2.
>
> This is a J2EE application, and we are going to be making extensive
> use of web services.
>
> Our design dilemma comes down to this:
>
> There is an arbitrary hierarchy of units (institutions) that the users
> can belong to, say:
>
> Corporate_HeadQuarters
> Division_1
> Sector_A
> Group_i
> Group_ii
> Sector_B
> Group_iii
> Group_iv
> Division_2
> Sector_C
> Group_v
> Group_vi
> Sector_D
> Group_vii
> Group_viii
>
> This hierarchy could be represented in a database table, called
> "Institution", for example.
>
> At each level of the hierarchy, we will collect records, say
> accounts_payable and accounts_recievable.
>
> Clearly, some Groups would like the option of keeping their records to
> themselves, but the application could easily handle this security.
>
> The development team is divided into three camps:
>
> "Camp A" (one database approach) - want to store the institutional
> hierarchy in a database table, store all "accounts_payable" and
> "accounts_recievable" data in single database tables, so there would
> be
> three tables, and it would be easy to figure out who owned which
> records
> by using basic SQL.
>
> "Camp B" (multiple database approach) - want to store the
> institutional hierarchy in one database, and then use separate
> databases, each with a "accounts_recievable" and an "accounts_payable"
> table. "Camp B" argues that this approach provides security at the
> database level, which it seems to. But it might also lead to
> maintenance issues when dealing with the backup and restore of
> multiple databases.
>
> "Camp C" (one database, multiple table owners) - want to use a single
> database,
> but copy the "accounts_recievable" and "accounts_payable" tables and
> give privileges only to the applicable group, I.E.,
> "group_i.accounts_recievable".
>
> In weighing these options, I have come up with the following pros and
> cons:
>
> One database approach (Camp A)
> ------------------------------
>
> Pros: * simple install,
> * allows easy roll-up accounting if needed,
> * simple tuning and maintenance
> * should scale to a large number of groups
>
> Cons: * does not provide database-level security - relies on the
> application.
>
> Multiple database approach (Camp B)
> -----------------------------------
>
> Pros: * provides database-level security, independent of the
> application,
> * separate databases reduces the complexity of the design a
> little
>
> Cons: * means creating a separate database per "group"
> * more databases mean more difficulty (steps) tuning and
> maintenance
> * only 256 databases per instance using DB2
> * many copies of the same tables, over and over again...
> * databases need to be cleaned-up if the institutions are
> shuffled, which is rare.
> * might be resource-intensive on a single-machine installation
> (a definite possibility).
>
> One Database, Multiple Table Owners (Camp C)
> --------------------------------------------
>
> Pros: * One database is slightly easier maintenance than the Multiple
> database approach.
>
> Cons: * Table-level security is almost as good as Database-level
> security - in fact they are logically identical, database-level
> security gives you a better feeling.
>
> Does anyone else see the Pros and Cons differently?
>
> I am in "Camp A" (several such designs in production) but I am moving
> towards "Camp B".
>
> I would like to hear any advice or opinions!
>
> Thanks,
> Rob.

        I suggest that you also consider one database with views. User groups can each have their own views, but you will have only
one database to maintain.  

 Pros: * allows easy roll-up accounting if needed,
       * simple tuning and maintenance
       * should scale to a large number of groups
       * provides database-level security, independent of the
application,

 Cons: * May be a bear to set up. Received on Mon Feb 11 2002 - 22:57:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US