CODEWITHSUNDEEP
sql-serversql-server-2005database-schema
CptSupermrkt
CptSupermrkt

Reputation: 7134

Database schema clarification

Unfortunately, the term "schema" has come to take on different definitions for different databases. We're using SQL Server 2008 R2, and with that in mind, I have a better understanding thanks to some other questions here with people asking similar questions. However, before I begin making the database, I want to be sure I have this right for my specific scenario.

Basically it's a database for various departments of the company. For example, Administration will manage employees with a bunch of tables related to employee management. Marketing will have a lot of marketing related tables. And tech support will have a lot of tech support related tables. These "groups" will probably never interact with one another, but they're all part of the same project, so I'm putting them all in one database, rather than three separate databases.

Am I correct in understanding that this means I would want three different schemas? So that for Administration, for example, the tables would be named:

Administration.Employees
Administration.VacationDays
Administration.EmployeeAddresses
etc.

and then for tech support, for example:

Techsupport.Clients
Techsupport.OpenIssues
Techsupport.ClosedIssues
etc.

And then am I correct in understanding that the PURPOSE of this, instead of just having every table in the dbo schema, is for A) organization purposes, and B) permission purposes (users with Techsupport schema access shouldn't be able to access the Administration schema, for instance). The idea I've come to in my head is that schemas in the SQL Server definition is that a schema is just like a virtual folder that groups related tables together.

I think this is right, after all the similar questions that I've read, but I just really want to be sure I'm on the right path before I get too far in and realize I'm doing it completely wrong.

Is throwing everything into the dbo schema and calling a day discouraged / not intended? Should you use a schema, even for small databases that don't necessarily need multiple schemas?

Thanks.

Upvotes: 2

Views: 337

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294437

Schemas support two primary purposes:

  • security container. Permissions can be granted on schemas and such permissions apply to all objects in the schema. Eg. GRANT SELECT ON SCHEMA::Administration TO [foo\bar]; grants the SELECT permission to any table in the schema, including future added tables.

  • namespace. You can deploy your application in the schema [CptSupermarkt] and know that your app has a very low probability of a name conflict with other applications.

The prevalent use is the first one because most apps are not concerned with side-by-side deployment with other applications and usually assume ownership of an entire database (if not an entire instance). However there are types of applications (eg. audit tools and monitoring apps) that use the namespace aspect of schemas (or, at least, most should use it...).

Upvotes: 4

Related Questions