When is it appropriate database design to use a separate schema?

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?

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.

StackExchange Database Administrators Q#51556, answer score: 4