Skip to Main Content
  • Questions

  • Hiding complexity from user but permit them to use my database

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Shraddha .

Asked: February 10, 2016 - 12:18 pm UTC

Last updated: February 10, 2016 - 4:53 pm UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

I have one database that contains all tables of salary details of company's employees and this database is used by other three companies for their employees.i want to make separate database for three companies so that if one company changes the database then the changes does not changes my database structure and also want that this database should not consume large space in server.so please suggest me what should I do to permit the companies to change my database without affecting actual database.
Awaiting for ur response sir

and Chris said...

There's no hard and fast rule for this. As you only have four companies, if:

- This number is unlikely to increase
- Each company is likely to have lots of custom changes to the tables

Then separate schemas may be the best approach. On the other hand if you're expecting:

- Lots more companies to come online and
- They're all mostly standard (few tables/columns specific to a given company)

Then a single schema with customer_id added to the relevant tables may be better. Ultimately you need to trade-off the extra complexity of more schemas vs. the likelihood they will be different.

As you're storing salary data you should make doubly sure you're securing this so companies can't see each-others information. Separate schemas makes this easier. VPD can help if you're sticking with one schema.

You can find thoughts on a more extreme version of this problem at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526095800346914577

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.