VPD vs Multiple Schemas

From: Shailesh <shailesh.saraff_at_gmail.com>
Date: 5 Oct 2006 04:13:14 -0700
Message-ID: <1160046794.284345.196060@e3g2000cwe.googlegroups.com>

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. Received on Thu Oct 05 2006 - 06:13:14 CDT