Should I use standard dbo or my own schema for shared tables in a multi-tenant database?
I have a database that is going to record data for different clients. Most clients have the same data requirements; however, this is not always the case. For different requirements, I'm going to create extension tables that are specific to their needs. For each client, I am going to create a schema and then I will put specific extension tables, views, etc. Into your scheme.
However, for shared data tables, should I create the ones under the default dbo schema, or should I create a new schema?
Thank.
The only reason to use the dbo schema is for convenience, so you don't have to plan out in detail the roles, rights, and requirements of the schema, especially. However, if you are already planning a database with multiple schemas, I would definitely recommend that you develop your own shared applications schema and leave the dbo schema for the explicit DBA / privved objects.
I have developed and written about a multi-tenant database here and here , which you will naturally find useful. This is primarily shared schema stuff, but the first article has many pointers to other articles, including some objects with multiple schemas.
source to share
I would create a schematic Common
. You don't want to give your users access to the schema dbo
if you can help. Especially if you have statistics, etc.
Schemas are a great way to separate namespaces as well as manage security. Take advantage of this and organize your databases as easily as possible. This makes it more readable as you go through this list of tables!
source to share