Introduction
In this article, I will cover SQL Server Domain Modeling with “M” language and show how “M” language modeling can be used to create a schema in SQL Server as the target storage database. We will look at the basic constructs of the language, T-SQL generation, and how this model looks in Visual Studio by connecting Entity Framework to the created model within the database. We will not, however, focus on any client side code for connecting any front end to the Entity Framework; there are plenty of other resources for that elsewhere.
Background
In 2009, what was known as codename “Oslo” was renamed as SQL Server Modeling. This was a deliberate move to solidify a domain modeling approach and in time roll this into SQL Sever. Oslo was made up of three core parts:
- “M language”: A modeling language to describe your model, which is driven by a tool call Intellipad.
- Quadrant: A visual tool for viewing models and relational data.
- Repository: A storage medium based on SQL Server for storage of models and data.
The initial theory of “M” was simple; developers and database designers like to use text to describe what they want within a model that is not directly related to either the application or the database storage. Also, it would be easier for, say, a C# developer or SQL developer to construct a model with a textual language than using a visual modeling tool. And, if you are a non-developer such as a business analyst, you can use “M” to describe what you are after rather than trying to create pseudo-code or a diagram that may be open to interpretation.
Summary: “M” describes your model independent of the solution application code and independent of the storage medium.
Domain Specific Language: DSL
Another foundation of SQL Server Modeling is DSL (Domain Specific Language) where you can create a specific or custom “M” type language, which you and your business users can agree on. In theory, this sounds like a great idea; but I struggle with the practicalities of this as business users can not grasp even the simplest design concepts, and if a custom language is not open to interpretation or confusion, then I don’t know what is.
Why model at all
What I have always struggled with (and I’m sure not alone) is where to start your design. Historically, I would have used an SSADM type approach and later in my career, some agile proto-type method. I’ve tried a lot of approaches over the years, and here are just a few:
- Full Modeling: on paper, using SSADM type methods, and fix the whole design; then start development without changing anything.
- Database First: model via the database and create a data application layer between the database and application logic.
- Code First: create my objects in C#, mock the database via code, and work on this until I was happy with my application, then go create the database and associated views and queries and so on (or lately let the code create the database).
- Prototype: continuously update and change based on customer feedback, changing both code and database continuously to reflect any changes. I find prototypes work best for smaller projects for getting concepts across to business users.
Using modeling, you can create your model and examine that without getting distracted with the front end application or storage issues. Let’s assume we are using “M” language with which Oslo can create a database from the model. A typical development route might go like this:
Phase 1
- Agree what your model is!
- Create model in “M”
- Generate database script for a standard SQL Server database
- Use Entity Framework to generate your C# code in 2010 for your objects
- Prototype application front end
Phase 2
- Agree what your changed model is
- Change model in “M”
- Generate database script for a standard SQL Server database
- Use Entity Framework to generate your C# code in 2010 for your objects
- Change prototype front end.
Phase (3..n) will follow as Phase 2 and so on.
This gives you a big advantage as you don’t have to go and change your database, base objects, and data access layer code as this can be auto-generated from your model and you can quickly move through a few design iterations before you find the correct fit for your application.
What does “M” bring to the table?
“An easy way to create your model without having to commit to code or commit to a database schema that will not commit to you either.”
This may seem a trivial statement, but if you have a significant man hour investment in a pre-production code base, it would take a very brave project manager or developer to admit it is better off scraping the current investment than to continue down a bad design path. This is how “technical debt” is so ingrained within a lot of systems; as the cost to scrap a design long-term may be cheaper, but the initial hit price is just too high to either the project delivery date or the personal cost to development leaders or team members.
Modeling with a generated database and base object layering will buffer this to a certain extent by encapsulating this away from your application. At the very least, it decreases development time and allows for more fluid change management on the project.
In junior developer terms: change my model and regenerate both database and base objects with ORM and then fix the front end. Note to the wise: Ensure to put in enough time to your model to reduce your issues later on.
Introduction to “M”
Now we will begin to look at the technology of “M”. I will focus on the Intellipad tool using “M” to create a model, which we will use to create a SQL Server database and then create an Entity Framework model from that database.
Download the Oslo CTP
Download the CTP from Microsoft with the link below.
Note that Microsoft moves stuff about and the above link might move without warning. Once you install the Oslo CTP, you can start Intellipad, which is shown below. Switch to “M Mode” as shown below:
This is what we are about to do:
- Introduction to M language
- Create a model example based on supply chain
- Creating the model in database
- Examine the model within the database
- Examine the model Entity Framework created from the database
Approach
If you read some other examples on M, then you will likely see data being added to the M language module, as you can mix modeling and data in the same file. I personally don’t think this is helpful or constructive in trying to explain some points of the M language. Instead, I will focus on what effect this modeling of the M language will have on the database and surprisingly how much T-SQL and time this can save you.
Quick introduction to the M language
“M” is a simple language. “M” builds on three basic concepts: values, types, and extents. The following concepts are defined in “M”:
- A value is data that conforms to the rules of the “M” language.
- A type describes a set of values.
- An extent provides dynamic storage for values.
Value types from M to T-SQL to C#
| M Type | T-SQL | C# |
Logical | bit | bool |
Integer32 | int | int |
Integer64 | bigint | long |
Text | nvarchar | String |
double | float | double |
Decimal28 | Decimal(28,6) | decimal |
DateTime | DateTime2 | DateTime |
Time | time | Time |
Date | date | Date |
Guid | uniqueidentifier | Guid |
We will create a nice simple Address Extent here.
First, we open Intellipad and place the Mode in “M Mode” and create a module as shown below for a simple address. If you click M Mode - T-SQL Preview, a right panel will be shown displaying the SQL your module will create.
Looks simple enough, but there are a few points to note.
Schema
Did you notice it creates a new Scheme called “Anything” in the database? This might be a problem if you use multiple modules with different names and create multiple schemes splitting your tables across them and not in the default schema “dbo” as is usual with SQL Server. In fact, I rarely see a database use anything else, but most of my database developments are usually driven by a single schema.
Renaming the module name to “dbo” might make the T-SQL a little easier to live with, unless you are used to running multiple schemas within the same database.
Below, I’ve marked how each element translates to its equivalent T-SQL action:
- M module = Schema
- Extent = Table
- Fields = Fields
So to quickly recap, each action on the M language module will have a direct effect on your database.
Fields
Take the PostCode field; this is mapped as shown below:
It should look rather obvious except for the question mark which indicates that the field can be a null.
So removing the question mark for the field definition will create an equivalent on T-SQL such as:
Types in M language
A type in M language can be described as a building block or an abstract class which you use to create further blocks and for reuse. Address is a good example of this. If you take the above example and place type in front of the Address extent, then you will notice that no T-SQL is generated.
There is no T-SQL generated because the type is not created within an Extent with an appropriate T-SQL member; it has to be inherited.
To use a type or multiple types, you have to create an Extent object and inherit the types as shown below:
You will notice that the Customer Extent is made up of both the Address type and the Contact type. You can reuse types in any number of objects. This can be very useful when designing a large schema for your system.
AutoNumbers and Identity/Primary Keys
Every database table usually has a Primary Key and normally that is an auto number based on an integer. This is a fairly simple construct for a Primary Key and one that is used more often than not. You can create Primary Keys based on test or any other combination of fields etc. M has an AutoNumber() which will set an identity on an integer field. So the line will look like this:
id : Integer32 => AutoNumber();
Couple this with the Identity constraint for the table (this does not have to be an AutoNumber) as shown below:
where identity id;
Putting this all together, we have the following:
You can also place your identity within a type and add in other common fields for a reusable type. An example below is fairly typical of a common type:
type Common :
{ id :
Integer32 => AutoNumber();
CreatedBy : Text(20)?;
CreatedAt : DateTime?;
EditedBy : Text(20)?;
EditedAt : DateTime?;
DeletedBy : Text(20)?;
DeletedAt : DateTime?;
} where identity id;
Other Primary Keys
Using a string as primary key:
type StringPK :
{ id : Text(20)
CreatedBy : Text(20)?;
} where identity id;
Using compound Primary Keys:
type StringPK : {
Key1: Text(20);
Key2 : Text(20);
} where identity (Key1, Key2);
Constraints
Constraints can be placed within your model with remarkable ease. A quick example below shows that a stock value can never be below 0.
This is done with a single addition to the stock field by adding “where value >0”.
The T-SQL it produces is fairly substantial for such a small piece of code; it adds a function and then adds that function as a constraint on the table it creates. Most Constraints are usually fairly simple so it can be very useful if you can place these in the model.
Default values can be done as shown below:
StockValue :
{ //Default Value 0
onOrder => 0 : Integer32;
};
Refactoring and reusable types
If you create your entities, you may notice that you have common types within your model. A typical example is addresses, where you may want a single address table.
Below is an example of reusable type for addresses used in Customer and Supplier.
module dbo
{
//Create Type Address
type _address :
{
id : Integer32 => AutoNumber();
Address1 : Text(20)?;
Address2 : Text(20)?;
Address3 : Text(20)?;
Address4 : Text(20)?;
PostCode : Text(20);
} where identity id;
//Create Collection of Address
Address: {_address*};
//Create Customer Entity
Customer :
{
id : Integer32 => AutoNumber();
Name : Text(20);
// Reference the address in another extent
AddressID : _address where value in Address;
} where identity id;
//Create Supplier Entity
Supplier :
{
id : Integer32 => AutoNumber();
Name : Text(20);
// Reference the address in another extent
AddressID : _address where value in Address;
} where identity id; }
This is a sample of the T-SQL generated:
create table [dbo].[Address]
( [id] int not null identity,
[Address1] nvarchar(20) null,
[Address2] nvarchar(20) null,
[Address3] nvarchar(20) null,
[Address4] nvarchar(20) null,
[PostCode] nvarchar(20) not null,
constraint [PK_Address] primary key clustered ([id]) );
go
create table [dbo].[Customer]
(
[id] int not null identity,
[Name] nvarchar(20) not null,
[AddressID] int not null,
constraint [PK_Customer] primary key clustered ([id]),
constraint [FK_Customer_AddressID_dbo_Address]
foreign key ([AddressID]) references [dbo].[Address] ([id]) );
go
create table [dbo].[Supplier]
(
[id] int not null identity,
[Name] nvarchar(20) not null,
[AddressID] int not null, constraint
[PK_Supplier] primary key clustered ([id]),
constraint [FK_Supplier_AddressID_dbo_Address]
foreign key ([AddressID]) references [dbo].[Address] ([id]) ); go
The SQL generated by the above example is fairly extensive and creates far more than I would have expected and even far more than I would have written manually. The SQL output is included in the sample files called Addesses.sql.
Creating a real world model
We will build a typical sales purchasing model, then run the generated T-SQL in SQL Server and examine the model. We have covered the basics of the “M” language and now will use these techniques to model and create a simple purchase and sales system.
Very brief specification
A purchasing and ordering system which will store customer and supplier information. There are sales and purchase orders which of each could have multiple items. Each order must have an employee associated with it. Also, each order will have an invoice and delivery address. Below is the complete model, which will generate a SQL script that will create the model within the database.
module dbo
{
//TYPE ADDRESS : UK Format
type _address
{
id : Integer32 => AutoNumber;
Address1 : Text(20);
Address2 : Text(20)?;
Town : Text(20);
County : Text(20)?;
Country : Text(30)?;
PostCode : Text(10);
} where identity id;
// Create a collection of Addresses
Address : {_address*};
//Type Employee
type _employee :
{
id : Integer32 => AutoNumber;
Name : Text(30);
Actice : Logical;
} where identity id;
//Create a collection of
Employees Employee : {_employee*};
// Type Header with
type _header
{
Name : Text(30);
Description : Text(250);
InvoiceAddress : _address where value in Address;
DeliveryAddress : _address where value in Address; }
type _common :
{
id : Integer32 => AutoNumber;
CreatedBy : _employee where value in Employee;
CreateDate : DateTime;
} where identity id;
//Item to be used
type _item
{
ProductKey : Text(30);
Qty : Integer32; ItemCost :
Double; TotalCost : Double;
}
//Purchase Line
type _purchaseline : _common & _item;
PurchaseLine : {_purchaseline*};
PurchaseOrder : _common & _header &
{
Line : _purchaseline where value in PurchaseLine;
};
//Create Sales Order Line
type _salesline : _common & _item;
SalesLine : {_salesline*};
SalesOrder : _common & _header &
{
Line : _salesline where value in SalesLine;
};
}
The model is made up of various types, which allows for creating concrete extents. As can be seen, both Sales and Purchase have headers and lines which are made up of the same types; this is very powerful and you can make crucial and large changes to your model very quickly.
The generated SQL is just too large to fit within this article, but to give you an idea, we constructed 80 lines of “M” which generated about 300 lines of T-SQL. But the model and the generated T-SQL are included with the download of this article.
Running the generated T-SQL
Running the generated T-SQL against a SQL Server 2008R2 Express database and this can be used to create a database diagram as shown below:
As you can see, the model created in SQL Server via the generated script gives a good representation of the “M” model. Note the strong relationships it has created with foreign key constraints linking the tables in a manner that was dictated by the model. Also, as dictated by our model, there is a single Address table that is matched to Sales and Purchase orders so all addresses are stored in a single table (this is a fairly common implementation).
Sales, Purchase Orders, and lines are made from the same combination of the same types but were created as distinct Extents, which is reflected in separate tables thus matching the model.
You will also notice that “*_label” tables are created for some of the tables, or to be precise, tables that have multiple foreign keys that depend on that table, such as Employee or Address.
Below is an example of the function which uses the Employee_Label table and Employee table for lookup support for Employees. If you really don’t like this, you can always remove it from the generated SQL script.
create function [dbo].[LookupInEmployee_Labels]
(
@name as nvarchar(max)
)
returns table
as
return
select top (1) [t3].[id] as [id], [t3].[Name] as [Name], [t3].[Actice] as [Actice]
from [dbo].[Employee_Labels] as [p]
cross apply
(
select [$Employee3].[id] as [id], [$Employee3].[Name] as [Name],
[$Employee3].[Actice] as [Actice]
from [dbo].[Employee] as [$Employee3]
where [$Employee3].[id] = [p].[Value]
) as [t3] where [p].[Label] = @name; go
As can be seen, the “M” model matches the created database closely. Now we will use Entity Framework and examine how the model from the database translates to Visual Studio.
Using the model within Visual Studio
Now we will look at using our model within Visual Studio. We will be using Visual Studio 2010 and Entity Framework 4 to recreate the model from the database that we earlier created. So add a new Entity Data Model to an application, then choose “Generate from database” as shown below:
Connect to your database
Add in the tables you wish in your model
Generated Entity Framework model from database
As can be seen, the model looks very similar to the model that was created within SQL Server. Entity Framework has also used the foreign key relationships to create navigation properties between the classes it has created to represent the tables in the database.
Navigation properties are a real strength of Entity Framework, as you don’t have to explicitly call new queries. They allow for navigation by association between entity types. A detailed explanation is beyond the scope of this article, but it is worth understanding its uses. I’ve included an MSDN link below if you are interested.
Summary
“M” can be a real benefit in model development and you can create models fairly quickly. “M” is a simplistic language and can be easily picked up by developers or database administrators. The use of reusable types and using multiple types within the one or more Extents gives good flexible power in model creation. Another strength is that you can quickly change a model, refresh a database, and use an ORM tool such as Entity Framework, and go through a few iterations to perfect a model.
The Future of SQL Server Modeling (Oslo)
Oslo or SQL Server Modeling as a technology has currently been shelved due to internal team reorganisations and a refocus on other technologies. This is not an unusual occurrence with internal Microsoft projects and some are either never seen again or pop-up in a different form later on. The basic concept of “M” as a language is an excellent one, and I hope it resurfaces in SQL Sever 2012 or later, as I find it an excellent tool and can really make you consider your options instead of heading down a route that you later regret. Using “M” to create or enhance Entity Framework modeling would be a good move as the current modeling tools are far from ideal. There are hints of this within the MVC 3.0 modeling, but this really is at the other extreme, concentrating on code first generation.
References
Note on online samples: There are a limited amount of online samples for M language, and the syntax seems to change wildly and does not match the latest Oslo CTP which you can download. This unfortunately degrades a very good technology which can offer a lot to a developer.
I have searched long and hard for references on Oslo and M language; this is the complete list of what I have managed to find:
