Re: VPD vs Multi Schema vs Partitioning

On 9 Jul 2006 04:51:06 -0700, xtanto_at_hotmail.com wrote:

>Hi Oracle Gurus,
>
>We will develope a web based ERP application for a company with multi
>branch (about 20), with the following requirement :
>- a normal user can only see and update data for his branch
>- some users from head quarters can see ALL data from ALL branch
>- reporting will be per branch, but there should be Consolidation
>reports
>
>Total user will be 200, but maximum concurrent is 60 users.
>
>We will use JSF(ADF Faces) + BC4J, one database server and one apps
>server.
>
>The question is :
>To meet the requirement above about which user can see which data, what
>Oracle feature should I use :
>is it VPD ?
>or should I just use different schema for each branch ?
>or use Partitioning ?
>
>Thank you for your recommendation,
>xtanto

Partitioning has *nothing* to do with your requirements, as you can't grant access to a partition.

The decision between VPD and multiple schemata can not be made on such insufficient information as above.
You need to ask yourself: how often do we need all branches? What is the performance impact of storing the data in 20 schemata, versus one schema with VPD. How complicated is the VPD algorithm? What is the performance impact of setting everything up in one schema and adding extra attributes to most tables. Or do you need to add attributes to *every* table, or only to a number. What is the impact of having to administer 20 schemata, as opposed to one?
Questions, I am afraid, you need to answer yourself, or answer them by hiring a consult.
I would wonder though why anyone would develop a webbased ERP application nowadays, with so many ERP applications on the market. It seems you are embarking upon a futile and costly exercise, where there is already so much stable software on the market. Your exercise isn't called 'reinventing the wheel', by any chance?

--
Sybrand Bakker, Senior Oracle DBA