Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am developing a database which has data sourced from many different applications. In my first pass at design, I placed the staging tables each in a schema named for their source application.

As several of the source applications have similar data and similar table names, I use the schema name to differentiate the source application. The alternative I am considering would be using a single schema and including the source application in the table name.

I wanted to look into the design rules pertaining to when to use a different schema and the pros and cons of doing so and I could not find anything.

Is the schema purely for permissioning and security?

Does it make sense from an organizational point of view to create objects in separate schemas beyond what is required for application development or is this just needlessly adding complexity to queries?

Are there any other repercussions of this decision which I have neglected to consider?

share|improve this question
    
For anyone else interested in this topic, I found this post dba.stackexchange.com/questions/14478/… which has some good information despite being a conversation among abandoned user ids ;-) –  Thronk Oct 16 '13 at 18:11
add comment

2 Answers

up vote 1 down vote accepted

From the description you gave I don't think security is the concern here. As you said your application receives data from multiple sources. The security comes/implemented when you have role based security kind of things on retrieval/access methods of that data. I am guessing the there must be first some kind of consolidation, analysis, reconciliation etc... happening before the data is is being presented to user.

In such cases separate schema is the best way to keep source data intact,not only during staging but even a original copy of source data; Just in case you want to troubleshoot later what data has been received from specific source.

Depending on what are the data providers but if this is financial data it is very likely the provider update source data format, add/remove columns etc.. frequently. if multiple source starts doing this at the same time it will be difficult to manage those changes if you have one consolidated schema design. but if you had seperate schema you know that this changes is not going to affect other sources data.

Second scenario if you are expecting you application as a source providing Some sort of consolidate data or source specific data to another subsystems then with separate schema you have more control and flexibility it terms of how and what you want to share. If you have same schema; sharing data will becom pain by Selecing some tables, or custom roles, or writing alternate APIs.

Well down side of having seperate schema is you have to use fully qualified object names to a life any unexpected results. Also if you have any scenario wher you might use open/distribute queries, linked servers, etc... Remember that it allows only two level of object name resolution. Think about other things like SQL agent jobs, and other features you might be using and check it supports fully qualified object name resolution or not.

share|improve this answer
1  
Platform note: In DB2, when running on IBM i, we generally use "system naming" to allow name resolution by a path of schema names (a "library list"). We often use them to segregate different operating environments, or discrete business entities. –  WarrenT Oct 16 '13 at 0:35
add comment

I am going to mention both logical and physical design here.

I know IBM recommends placing staging tables into their own schema(s) and even into their own table spaces (database implementation specific to DB2 in this case) for reasons of management. Schemas allow you to manage better via separation of concerns and security. The tablespaces (physical design) allow you to better handle maintenance issues regarding REORGs, RUNSTATS, and backups.

While you may not be implementing IBM DB2, I think their philosophy is something worth thinking about and implementing.

share|improve this answer
    
To be exact, there may not be many security benefits when using different schemas in DB2 compared to other database products. The fact itself that table T is placed in schema A does not give user A any particular privileges over that table. Similarly, this by itself does not prevent user B from accessing the table. For example, in the default configuration user B can create table A.T1, and user A won't be able to read from it. In DB2 schemas are purely logical constructs allowing you to group database objects and may be provide default name resolution. –  mustaccio Oct 15 '13 at 20:28
1  
@mustaccio - to be a bit more exact, if the DB2 platform is IBM i, using system naming, then security could be managed in part by schema. –  WarrenT Oct 16 '13 at 0:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.