Should I use the default dbo or a custom schema for common tables in multi-tenant database?

Question

I have a database that's going to record data for different customers. Most of the customers have the same data requirements; however, that's not always the case. For the different requirements, I'm going to create extension tables that are going to be specific for their needs. For each customer, I'm going to create a schema and I will then put the specific extension tables, views, etc under their schema.

However, for the common data tables, should I create those under the default dbo schema or should I create a new schema instead?

Thank You.

Answer 1

I would create a Common schema. You don't want to give your users access to the dbo schema if you can help it. Especially if you have stats, etc there.

Schemas are a great way to separate namespaces as well as administer security. Take advantage of that and organize your databases as simply as possible. It makes it much more readable when you're going through that list of tables!

Answer 2

The only reason to use the dbo schema is convenience, so you don't have to plan your schemas roles, rights and requirements out in great detail. However, if you are already planning a multi-schema database, then I would definitely recommends that you plan out your own s common application schema, and leave the dbo schema for explicit DBA/privved objects.

I have designed and written about multi-tenant databases here and here, which you nmight find useful. This is primarily shared-schema stuff, but the first article has a lot of pointers to other articles including some multi-schema stuff.