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

Keep schemas separate or joined?

I have three different schemas, each comprise about 15 tables. I am planning to merge them into one schema as they are related in a loose way. They belong to three applications that work together (80% of the time) or separately (20%). What are the disadvantages and advantages of keeping them separate or joining them together?
The biggest disadvantage to this proposal is with security. If all database objects are in the same schema, you will not be able to stop one application from using or access another applications database objects. You can also run into problems with managing these applications. Should there ever be a need to shutdown access for one application but still allow access for the others, you can simply lock the account. But you won't be able to lock the account if all objects are in the same schema without affecting the other two applications.

The advantages of using one large schema is that it makes application development easier since you don't have to worry about permissions between schemas. And it can be easier to administer one schema over multiple schemas. But in my opinion, three schemas is not that much to deal with. Many databases have more than three schemas.

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.