CODEWITHSUNDEEP
sql-server
Jonathan Allen
Jonathan Allen

Reputation: 70307

SQL Server 2008 Schema Naming Conventions

I'm creating a brand new database with no legacy constraints, so I'm curious as to what the schema best practices are.

The database will be called "SecurityData". It stores information about bonds.

The schema I have already identified are:

Questions:

  1. Am I going schema crazy or does this make sense?
  2. Should I use dbo for my "real tables" or should I avoid that schema as it tends to become a garbage dump?

Upvotes: 4

Views: 1074

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294217

Schemas serve a dual purpose:

  • security containers. Grants/deny/revokes on a schema apply to all objects in the schema. Separating related security objects into a shcema allows for easy maintenance and control of access.
  • namespaces. Qualifying object names with schemas allows reduced conflict probability with names used by other applications and even other modules within your own application.

So my question to you is: why do you want to use schemas in the first place? I'm not saying you shouldn't, but i want to understand which advantage of the schemas are you most appealed to. If you know the answer to that, then you'll know how many schemas you need and what those schemas are. Of course, the answer can be a mixture of the two reasons I give at start, that is OK. In that case you may find that what makes sense from a namespace point of view is a disaster from security point or view or vice-versa.

I myself I used separate schemas just like you plan to, and soley for programming namespace benefits. during development it helped me to see, just from the name of an object, where to it belongs logically in the app.

Upvotes: 4

Related Questions