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

First I want to thank everyone who replied to my post - Jerry G, Ruud, Niall, David, Jerry S, Dirk, Doug, and Robert - I appreciate your help and your thoughtful comments.

Second, I wanted to update you on the proposed solution that the team arrived at today.

I mention that it is a proposed solution because we need to do a test implementation, and check that this design will work and meet our needs.

At this point, I am happy with the solution, but it is NOT a conventional design. I am eager to check if it will work for us - I will post a follow-up.

First, we decided to separate the users and the Groups into a separate database. For a number of reasons, this "Authentication" database will be queried via a web service. Also, we decided to try to use GUIDs for the UserID and GroupID columns.

Second, we decided that we could also gain benefit from making the PK in "accounts_recievable" and the PK of "accounts_payable" GUIDs.

Thus, we have arrived at a solution where we can safely use the UserID GUID as a FK in the "accounts_recievable" and "accounts_payable" tables - even though they are in a separate database - as long as we can garentee, (if necessary by means of a distributed transaction or two-phase commit), that the UserID GUID is always valid.

We will be very, very careful about the way we code the application to ensure cross-DB referential integrity.

The development team backs this approach, since it allows the "accounts_recievable" and "accounts_payable" tables to reside in EITHER a single-database ("Camp A") or several separate databases ("Camp B"). Thus the development team is confident that the system can achieve high-scalability, if necessary, by using separate databases - while also achieving high-security by isolating the data. (Personally, I see the data security as being roughtly equivalent in both schemes, as did several people who replied to my original post.)

I am convinced that if the hierarchy ("Institutions") is stored in a separate "Authentication" database, then it would be easy to move "core data" between a single-database and multiple-database solutions, if needed.

Any comments about this proposed solution? Maybe I'm nuts... but I think it will work for us...

The primary target RDBMS is DB2 version 7.2, although I hope to target both SQL Server 2000 and Oracle 8i/9i as well...

SQL Server 2000 supports GUIDs natively (uniqueidentifier type), but I think that our middle tier will have to generate a GUID for the DB2 v7.2 and Oracle 8i/9i RDBMSs - unless we can code GUID generators for them as functions or other primitives - does anyone know?

Please Let Me Know What You Think & Many Thanks Again... -Rob.

robhindman_at_hotmail.com (Rob Hindman) wrote in message news:<d4199cf5.0202111920.5d5abec7_at_posting.google.com>...
> 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.
Received on Wed Feb 13 2002 - 00:57:17 CST