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

I vote for A: one db for all. The DB design shouldn't be driven by maintenance and peformance
and not security considerations.

If its a web app, then you're likely to be connecting to the DB via some generic account from the web server so you may not be able to use db security. I suggest row-level security be impemented as a separate layer; passing userids around stored procs etc that have nothing to do with security is going to make coding slower and be a maintenance nightmare.

The only times I have gone for horizontal partitioning (multiple dbs) is when the units were completely distinct and it was reasonable to implement any cross-unit stuff as a separate application.

michael g
"Jerry Stuckle" <jstucklex_at_attglobal.net> wrote in message news:3C692CEB.40D_at_attglobal.net...> Rob,
>
> I agree with Jerry Gitomere here. You can effectively implement Camp
> "C" with views. This way you will have both security and performance.
>
> As in Oracle, performance suffers when you need to join across multiple
> databases. And there are several other problems with Camp "B" - like
> what if you need foreign key constraints?
>
> I believe in the KISS method. One table each: hierarchy, accounts
> receiveable and accounts payable. A view on each of the account tables
> for each of the institutions in the hierarchy. Apply row level security
> to the account tables via the views.
>
>
>
> 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.
>
> --
> ====================================
> To reply, delete the 'x' from my email
>
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex_at_attglobal.net
> ====================================
>
Received on Tue Feb 12 2002 - 18:42:09 CST