SQL Server: conventions for naming a schema

Question

Consider a database server whose job today is to house one database. Likely the database will be moved in the future to another database instance which houses multiple databases & schemas.

Let's pretend the app/project is called Invoicer 2.0. The database is called AcmeInvoice. The database holds all the invoice, customer, and product information. Here's a diagram of the actors and their roles and behaviour.

The schema(s) will largely be used to easily assign permissions to roles. The added benefit here is that the objects aren't under dbo, and that the objects & permissions can be ported to another machine in the future.

Question

• What conventions do you use when naming the schema?
• Is it good form to name the schema the same as the database?

Answer 1

I would think that if your schema name ends up being the same as your database schema, then you are just adding redundancy to your database. Find objects in your database that have common scope or purpose and create a schema to relect that scope. So for example if you have an entity for Invoices, and you have some supporting lookup tables for invoice states, etc, then put them all in an invoice schema.

As a generally rule of thumb, I would try to avoid using a name that reflects the application name, database name or other concrete/physical things because they can change, and find a name that conceptually represents the scope of your objects that will go into the schema.

Your comment states that "the schemas will largely be used to easily assign permissions to roles". Your diagram shows specific user types having access to some/all tables or some/all stored procs. I think trying to organize objects conceptually into schemas and organize them from a security standpoint into schemas are conflicting things. I am in favour of creating roles in sql server to reflect the types of users, and grant those roles access to the specific objects that each user type needs, as apposed to granting the role or user access the schema to build your security framework..

Answer 2

Why would you name the schema the same as the database? This means all database objects fall under the same schema. If this is the case, why have a schema at all?

Typically schema's are used to group objects within a common scope of activity or function. For example, given what you've described, you might have an Invoice schema, a Customer schema and a Product schema. All Invoice related objects would go into the Invoice schema, all Customer related objects would go into the Customer schema, and the same for Products.

We often will use a Common schema as well which includes objects that might be common to our entire application.

Answer 3

I would call the database AcmeInvoice (or another suitable name) and the schema Invoicer2.

My reasons are as follows: Acmeinvoice means I am grouping all of that applications objects/data together. It can therefore be moved as one unit to other machines (a backup/restore or unattach/attach).

The schema would be Invoicer2. Applications change, maybe in the future you will have Invoicer21 (you would create a schema), or perhaps a reporting module or system (Reports schema). I find that the use of schemas allows me to separate data/procedures in one database into different groups which make it easier to adminster permissions.