On Oracle I'd implement one database, and a system of views. It might also
be a candidate for row-level security. However and this is the clincher to
get good advice you'll need to make sure you get the advice from someone who
knows DB2 inside out.Databases are not analagous creatures what works well
in one may work poorly in another, so one should IMO never do generic design
but always design for the platform you are using.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Ruud de Koter" <ruud_dekoter_at_hp.com> wrote in message
news:3C68C772.490521BC_at_hp.com...
> Hi Rob,
>
> 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!
> >
>
> Some words from someone with an Oracle-background (so I do not know
whether this
> applies to DB2 as well).
>
> What strikes me is that you do not give any serious drawback to option C.
The
> only point you make is that 'database-level security gives you a better
> feeling'. Now feelings can be pretty important, but in this terrain I 'd
like to
> see them firmly grounded in reasoning. As you already state, user-based
security
> (or table-level as you call it) is logically equivalent to database-level
> security. Given this fact, the simpler structure of the user-based
solution
> should take precedence.
>
> In an Oracle environment the user-based solution is quite likely to
outperform
> the database-based solution as soon as links have to be made between
different
> users/databases. This is caused by the simple fact that the optimizer
works at
> the database-level, based on knowledge of the data in a single database.
As soon
> as more than one database is involved, easy assumptions are made about the
data,
> which sometimes leads to very suboptimal execution plans (or at least this
used
> to be the case, haven't tried to suboptimize execution in ages ;-). Add
this to
> easier management/maintenance, and my opinion should be clear!
>
> Regards,
>
> Ruud.
>
> --------------------------------------------------------------------------
------------
> Ruud de Koter HP OpenView Software Business Unit
> Senior Software Engineer IT Service Management Operation
> Telephone: +31 (20) 514 15 89 Van Diemenstraat 200
> Telefax : +31 (20) 514 15 90 PO Box 831
> Telnet : 547 - 1589 1000 AV Amsterdam, the Netherlands
> Email : ruud_dekoter_at_hp.com
>
> internet: http://www.openview.hp.com/itsm
> http://www.openview.hp.com/assetview
> intranet: http://ovweb.bbn.hp.com/itservicemanager
> --------------------------------------------------------------------------
------------
Received on Tue Feb 12 2002 - 05:56:34 CST
