Question about schemas and tablespaces and users

Aloha all-

Okay, this has been bugging me for awhile, and I'd like to get it straight in my head once and for all.

I understand why you would have multiple tablespaces, and as they directly relate to files on the local machine. As the example goes, you can physically seperate your "accounting data" and your "manufacturing data" into different tablespaces, which could reside on different disks, etc. All good things.

My confusion stems from the use of schemas and users. Having now already broken down the manufacturing and accounting data into different tablespaces (which in SQL Server parlance would be described as "databases"), why have schemas? And why should *every* user have a schema?

Don't get me wrong, I like having the granularity insofar as that I can work in my schema on a section of the database, while other developers can work on their pieces, but as far as I can tell, that's where the necessity ends. In a production environment, where we have, say, a hundred users logging in using either a web front end or client application (say a fancy cash register), why should they all have schemas too?

The solution we've been using is to create a PROD schema and put all the related table in that (and have a PROD schema for each tablespace), but having all these users with their own schemas seems silly and unnecessary.

So my question is simply, how should I be thinking of users and schemas? Is there a better (or more correct) way to group tables for a production environment? Must all users *have* a schema (can some just be simple consumers of data?)?

Thanks for setting me straight on these points,

Theo Received on Thu May 03 2001 - 10:10:33 CDT