Schema Design Patterns
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
SQL Server Modeling Services uses SQL Server schemas to group related database objects that describe a particular problem domain. This Modeling Services design pattern helps to promote the organization of models within the database. Tools that explore the Modeling Services database are able to use the SQL Server schemas to group related objects for users, and the schema names themselves help to identify the overall model or area related to a database object.
Tip
This topic explains how to use the Modeling Services pattern for schemas directly in SQL Server. If you are creating models with Microsoft code name “M”, refer to the topic on using this Modeling Services pattern in “M”. For more information, see Using Modules to Scope the Model.
Guidelines for Using Schemas
The following list covers the guidelines for using SQL Server Schemas in the Modeling Services database.
Create a SQL Server Schema for each Logical Area within a Model
Grant Control to the RepositoryAdministrator Role and the RepositoryService User
Use Compound Schema Names to Express Schema Relationships
Create Database Objects within a Schema
Use Extended Properties for Schema Version and Copyright Information
Note
Database administrative privileges are required for manually adding and managing SQL Server schemas in the Modeling Services database.
Create a SQL Server Schema for each Logical Area within a Model
Models are used to describe logical areas or problem domains. Create a SQL Server schema in the Modeling Services database for each of these areas. The actual granularity of this area will be determined by the modeler. For example, a custom model of a group of servers might have a schema named Server to contain entities that describe those servers. However, for a large deployment environment, it may be beneficial to use the Server schema for general properties about all servers while using more granular schemas, such as Server.Database, to describe information that applies only to a specific server type. All Modeling Services SQL Server schemas should be owned by the special RepositoryOwner user.
For consistency, use singular schema names, such as Server, as opposed to plural schema names, such as Servers. Also consider adding a company name to all custom created schemas in the format of CompanyName.SchemaName. This improves the uniqueness of the schema names and improves schema organization within the Modeling Services database.
Example
The following T-SQL example creates a SQL Server schema named Server that is owned by RepositoryOwner:
use Repository
go
create schema [Server] authorization [RepositoryOwner]
go
Grant Control to the RepositoryAdministrator Role and the RepositoryService User
Modeling Services administrators need the ability to fully manage the schema and all database objects owned by the schema. This is accomplished by granting the control permission on the SQL Server schema to the RepositoryAdministrator role. Modeling Services also has a special user named RepositoryService. This RepositoryService user requires permissions on the SQL Server schema for delete, execute, insert, update, and select.
Example
The following T-SQL example applies recommended permissions to a schema named Server for both the RepositoryAdministrator role and the RepositoryService user:
grant control on schema::[Server] to [RepositoryAdministrator] with grant option
go
grant delete, execute, insert, update, select on schema::[Server] to [RepositoryService]
go
Use Compound Schema Names to Express Schema Relationships
It is often useful to express a hierarchical relationship between related schemas. SQL Server 2008 does not support expressing relationships between schemas. The Modeling Services database uses a compound naming convention to express these relationships. For example, consider a data model of a Human Resources organization with an associated SQL Server schema named HumanResources. This schema might contain tables and views that apply to the organization as a whole. However, the modeler might want to more specifically separate the data about employees. In that scenario, it would be useful to place the employee data in a separate SQL Server schema. By naming this new schema HumanResources.Employee, the connection between these schemas is more easily recognized. Tools that understand this convention might also be able to provide a richer experience by visually highlighting these relationships. Note that it is important to use brackets around a compound name that uses a period. In general, use brackets around object names in T-SQL statements to avoid confusion and query errors.
Example
The following T-SQL example creates two schemas that have a logical relationship through the applied naming convention:
use Repository
go
create schema [HumanResources] authorization [RepositoryOwner]
go
create schema [HumanResources.Employee] authorization [RepositoryOwner]
go
Create Database Objects within a Schema
Once the SQL Server schema is created for an area of interest, objects must be added to this schema. This provides organization for the database objects that together define the data model for a particular schema.
Example
The following T-SQL example creates a Shape schema and then adds two tables to that schema:
use Repository
go
create schema [Shape] authorization [RepositoryOwner]
go
create table [Shape].[SquaresTable]
(
[SideLength] int not null
)
create table [Shape].[CirclesTable]
(
[Radius] int not null
)
go
Note
Note that the preceding example uses a naming convention for Modeling Services tables. For more information about table naming conventions, see Table Design Patterns.
Use Extended Properties for Schema Version and Copyright Information
Use SQL Server extended properties to attach version and copyright details to schemas. Extended properties are a SQL Server feature that allows you to associate named values with any database object. These values are set with the sp_addextendedproperty stored procedure and retrieved with the fn_listextendedproperty function.
Example
The following T-SQL example adds version and copyright extended properties to the Shape schema that was created in the previous example:
use Repository
go
-- Copyright
execute sys.sp_addextendedproperty
@name = N'Copyright',
@value = N'© Microsoft Corporation 2008. All rights reserved.',
@level0Type = N'Schema', @level0Name = [Shape];
-- Schema version
execute sys.sp_addextendedproperty
@name = N'MajorVersion',
@value = 0,
@level0Type = N'Schema', @level0Name = [Shape];
execute sys.sp_addextendedproperty
@name = N'MinorVersion',
@value = 1,
@level0Type = N'Schema', @level0Name = [Shape];
execute sys.sp_addextendedproperty
@name = N'BuildVersion',
@value = 0,
@level0Type = N'Schema', @level0Name = [Shape];
execute sys.sp_addextendedproperty
@name = N'RevisionVersion',
@value = 0,
@level0Type = N'Schema', @level0Name = [Shape];
See Also
Concepts
SQL Server Modeling Services Design Patterns