VPD vs Multiple Schemas

[shailesh]

We are into healthcare software and now customers are requesting new feature from our application, they want to run application in their group of hospitals on single Oracle database. Available options are listed below. what do you suggest? if I am missing any point please let me know

Single Schema (using Virtual Private Database)
----------------------------------------------
Maintaining all database objects in the main schema and create user for each hospital to access data from main schema. Hospital id will be defined for each hospital and will part of PK of all tables. Security policy based on hospital id can be defined for each table, which allows other users to do DML (data-manipulation) as well as SELECT operations only on those records in a table, for which a user has access.

Application Level
-------------------
Advantages: No changes are required in our application.


Database Level
-----------------
Advantages: Easy to manage and maintain
Easy to upgrade and patch (Oracle)
SGA can be defined adequately
Advantage of sharing Sqls

Disadvantages: VPD concept may sound complicated (to Management)
Downtime of All hospitals will be the same incase our application version needs to be upgraded
Performance overhead (10%?) for SELECT while doing join operation or sub select
Backup time will be more


Multiple Schema's:
------------------
Create Schema for each hospital and maintain all schemas in single database. Each User will access own schema objects.

Application Level
-----------------
Disadvantages: All References to main schema needs to be removed from our application. (e.g. Schemaname.TableName)


Database Level
---------------
Advantages: Looks Simple and easy to understand (to Management)
Downtime can be planed for each Hospital for upgrading our application version
All hospitals can run on different versions of our application
Schema level backup can be scheduled

Disadvantages: Difficult to manage and maintain xx schema's
Difficult to upgrade and patch (Oracle)
SGA (Oracle Memory) to increase xx times (shared sql will not work)
Consumes more disk space (data, archive logs)
Accessing the Oracle data dictionary will have performance overhead
latching on the shared pool will be more


Please let me know.

Thanks & Regards.

[jhmartin]

If the hospitals might want to be on different versions then VPD is out. If different versions might require different Oracle versions then you need at least a small set of Oracle instances to cover the version requirements.

I strongly advise against putting multiple hospitals in the same schema w/o VPD, the security problems could be serious.