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

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. Received on Tue Feb 12 2002 - 04:20:50 CET