Q
Problem solve Get help with specific problems with your technologies, process and projects.

One schema or separate schemas for 3,000+ users

I am going to have 3,000+ users with accounts in a database, and each user can consume up to 5GB of disk space. Do I set up one schema for each of the users, or should I have one schema where everyone puts they data?

Hi Brian. I have an Oracle DB design question that I would like your advice on. I'm going to be using Oracle 10g with interMedia to store video, audio, photos and unstructured content inside the database. It's going to be a commercial application over the Web, and potentially I could have 3,000+ users with accounts in the database, and each user can consume up to 5GB of disk space. What I'm struggling with is, do I set up one schema for each of the users (each schema will have three tables, 50+ views, three sequences), or should I have one schema where everyone puts they data and use something like a Global Application Context to ensure that each user only has access to his/her own data within the large table? Anytime someone wants to grant access to their data to another user, a view is created.

Needless to say, both options look pretty bad. Either I have 15,000 tbales and contention on the Data Dictonary/lots of hard parsing, or I have three tables being shared by 3,000+ users and those three tables will probably be 7TB or larger in size.

Any advice?

Personally, if I were armed with the information above, I would use one set of tables for all users. I would then implement a Virtual Private Database (VPD) using Global Application Contexts so that a user can only see their data. If you are worried about large tables, then look at partitioning to help you out.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close