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

From: Doug Miller <dmiller_at_foresightpartners.com>
Date: 12 Feb 2002 10:59:26 -0800
Message-ID: <32b6c9cc.0202121059.13e386bb_at_posting.google.com>


robhindman_at_hotmail.com (Rob Hindman) wrote in message news:<d4199cf5.0202111920.5d5abec7_at_posting.google.com>...

> There is an arbitrary hierarchy of units (institutions) that the users
> can belong to, say:

...

> At each level of the hierarchy, we will collect records, say
> accounts_payable and accounts_recievable.

> "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.

I vote with Camp A for a few different reasons. I think when you really start talking about how to build your hierarchy table, it won't be a trivial table to design or implement. You might even have to try it, test it, decide it works, then roll it out. Down the road, you might discover you want some features added. (Hint - look at recursion, parent child examples, and spend some time thinking about normalizing it somewhat.)

Further, I've never seen a successful two table accounts receivable system before, or accounts payable for that matter. So if it takes "5 tables" to make a realistic, long term, AR system, 3 or 4 for simple AP, and then you try to scatter that out across multiple locations, or DB's, and then try to maintain it, you will have lots of "opportunities" in the future. Might ask the bean counters if they want a GL tie in just for fun.

> "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.

And if you are all politically under one super umbrella, and the BIG BOSS ever asks how much money we are owed across all divisions, someone is gonna have an uncomfortable discussion explaining why we can't do that.  

> "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".

Stored procedures with authentication? Stored procedures with some security stuff built in might be easier then maintaining all the views in the long run. Pass the Sproc a secret code that authenticates the fact that a connection is for a specific division, and the one Sproc will return the data for all divisions. Yes, this doesn't follow most OEM's security implementation, but 350 separate views gets old also when you discover that someone wants to add a field to a table, and you get to go "fix" all the views, test them, and verify security.

Bottom line. Don't break your data up into separate physical little pieces without danged good reasons. Down the road, it is easier IMHO to maintain and secure huge, consistent data chunks then a bunch of little ones floating hither and thither.

-Doug Miller Received on Tue Feb 12 2002 - 19:59:26 CET

Original text of this message