The Busy Developer's Guide to SQL Server Modeling
Shawn Wildermuth
March 2010
I am a data guy. I love the nature of how data is designed, shaped and consumed. I have spent countless hours poring over database diagrams determining the right way to represent some data that I have to work with in a system. While there are a lot of different ways to do that data design, in most cases I have had to work with graphical design tools to create my data. The problem is that I am a developer. Most of the work I’ve done over the last couple of decades has been to write text that is translated into computer instructions. Working with graphical tools to perform tasks is still uncomfortable as I tend to translate the ideas I have in my head back and forth between the concept and the graphic interface. But I think in text so maybe there are other solutions that don’t require a graphical design tool.
SQL is just text so it should be easy to create our model this way, right? Well here is a little snippet of the SQL I would need to create a simple table for tracking blog entries:
CREATE TABLE BlogEntries
(
Id int not null identity,
Title nvarchar(100) not null,
Body nvarchar(max) not null,
IsPublished bit not null default 0,
PublishedDate date null,
EntryAuthor int not null,
constraint PK_BlogEntries primary key clustered (Id),
constraint FK_BlogEntries_EntryAuthor_Authors
foreign key (EntryAuthor)
references Authors (Id)
)
There is nothing wrong with this approach, but it does require that I know quite a lot about the database structure. It’s close to the metal and therefore means I have to think of relationships as constraints, not navigable structures. This approach is not wrong, I just don’t find that its easiest solution. Of course part of the goal is to be easier than the graphical approach so let’s try something else. Since I love code, maybe that’s a solution.
Creating a data model with SQL or even graphical tools make the assumption you’re working from the database and pushing that design up through the application. To create my model perhaps I reverse this and create my model as classes that I can then push down into the database:
public class Game
{
public int GameID { get; set; }
public string Name { get; set; }
public DateTime ReleaseDate { get; set; }
public decimal Price { get; set; }
public Rating GameRating { get; set; }
}
Using classes like these to build the database is pretty common these days. This solution makes some assumptions that the database is a simple serialization store instead of a database. But unfortunately it relies on some other tool to map what is in a class with what is possible in the database. Since the database is relational and classes are structural it relies on the database generator to make the same decisions you’d make if you were building it by hand. Of course maybe the nature of your project doesn’t require you to tune the database and you can treat it as simply a data storage mechanism. In my experience the form and structure of the data matter. Whether the structure matters because of performance or business intelligence functions, the data will live forever…and the app is unlikely to live that long. Is there a solution somewhere in the middle? Yes, SQL Server Modeling.
Introducing SQL Server Modeling
Previously called Microsoft code name “Oslo”, SQL Server Modeling is a forthcoming set of technologies (presently in CTP) for solving problems around modeling data in your applications. SQL Server Modeling (or SSMod) enables you to describe your models, store them, and edit them. This represents a full life cycle for your models and the system simply becomes a runtime to consume the data contained in the models. Let’s see how this would work in a simple example.
Modeling in “M”
For our example, we might want to build a simple blog engine using ASP.NET and SQL Server. The SQL Server Modeling November 2009 CTP includes templates for Visual Studio 2010 that helps you create projects that include SSMod models directly. Figure 1 shows adding a new ASP.NET project with this support. (Note that the project names still contain the old “Oslo” code name in the November 2009 CTP; these items will be renamed in the future.)
Figure 1: Oslo Web Application
The new project will contain a new ASP.NET project but also have an “M” language file that can contain your textual representation of your model. “M” is a declarative language for defining models and working with data in a variety of ways. For example, for the blog you may need to model an author of a blog entry. In “M”, the shape of the Author can be created as a type:
module BusyDev
{
type Author
{
Id : Integer32 => AutoNumber();
Name : Text(100);
EMail : Text(100);
Url : Text(100);
IsValid : Logical => false;
}
}
In “M”, the model just supplies a container for the model definition. The type describes a shape. Specifying a type does not mean that you are designing a table, but simply a way of describing the shape of some data to be stored. The type allows you to specify different ‘fields’ of information and what types of data can be stored in the. For example, in the Id field, it specifies that we are storing 32-bit integers as well as using an auto-numbering scheme to fill the Id. The ‘=>’ operator specifies a default value for the field. For the text fields we have, the type of data is ‘Text’ but we are using a size of 100 characters wide for each of these.
Once we have specified the type in our “M” file, we need to specify how it is going to be stored. These are called extents. An extent describes how the type is stored in the data store but still in a database-agnostic way. For example to store individual author entities in the data store, we can create an extent by naming it then specifying a collection type as shown below:
Authors : {Author*};
The name of this extent is Authors and contains a collection of Author objects. The brackets tell “M” that you are specifying a collection and the asterisk specifies the type of collection (asterisk is zero or more, whereas a plus sign is one or more). Typically extents will also contain constraints on the data stored in them. For example, in our Authors extent we will want to specify a primary key as well as some unique constraints like so:
Authors : {Author*} where identity Id && unique(Name) && unique(Email);
These constraints let you specify simple constraints like the identity and unique constraints or even more complex ones using a LINQ-like syntax.
The “M” language for specifying these shapes becomes even more interesting as you look at how relationships are modeled. For example let’s look at a type for our BlogEntry:
type BlogEntry
{
Id : Integer32 => AutoNumber();
Title : Text(100);
Body : Text;
IsPublished : Logical => false;
PublishedDate : Date?;
EntryAuthor : Authors;
};
Note that the BlogEntry has a field called EntryAuthor that is Authors. Now Authors is the extent we created earlier so that implies that the author of this blog entry will be contained in that extent. Essentially this creates the foreign key and relationship between the BlogEntry and the Author. No more having to manage each of these relationships carefully. The resulting database table will be created with the right primary key/foreign key types as the model matures.
Building the Database
The project type you created in Visual Studio 2010 is special in that it expects to take any and all “M” files in a project and use them to create a database. Currently on every build the database is recreated. To get this functionality working, you have to specify in the project a connection string to a database to store your new model in. If you look at the properties of the project, you will see an M Deployment tab as shown in Figure 2.
Figure 2: Setting up M Deployment
By supplying the connection string, you’re enabling the project to rebuild this database as part of the development process. One of the things to be aware of is that you are getting a clean database on every build so that you may want to specify initial data for your model. The “M” language supports this as well. By specifying instance data in our model, this data will be put in the database during deployment. For example, to add myself as an author to my model, I can do the following:
Authors
{
{
Name => "Shawn Wildermuth",
EMail => "shawn@agilitrain.com",
IsValid => true,
Url => "http://wildermuth.com"
}
}
The name of this section (Authors) needs to match the extent to store it in, then the inner collection (inside the inner brackets) must match the shape of the object stored in the extent. In our case this is the Author type. Note that we are only specifying the fields that are necessary to store it in the data store. The Id field is automatically created for us.
You can also specify more than one instance by separating them by a comma like so:
Authors
{
{
Name => "Shawn Wildermuth",
EMail => "shawn@agilitrain.com",
IsValid => true,
Url => "http://wildermuth.com"
},
{
Name => "Chris Sells",
EMail => "csells@sellsbrothers.com",
IsValid => true,
Url => "http://sellsbrothers.com"
}
}
Working with the Model
At this point you can see how we can use the modeling features of the “M” language and Visual Studio’s M Deployment to take our schema and data and put them in the database during builds. But what about accessing that data in your own applications? As part of building your “M” file, the Visual Studio template also creates an .edmx file (from the ADO.NET Entity Framework) based on the “M” file. If you have not used the ADO.NET Entity Framework, you can get more information about it here: http://msdn.microsoft.com/en-us/data/ee712907.aspx. That means we can get the data and the data layer from the same set of artifacts. For example, once we have the “M” model specified, an Entity Framework context is created based on our module name followed by “Container” (i.e. BusyDevContainer). It is just like any other Entity Framework context in that it can be used to query the database with LINQ:
using (BusyDevContainer ctx = new BusyDevContainer())
{
var qry = from b in ctx.BlogEntries
.Include("Author")
where b.IsPublished == true
orderby b.PublishedDate descending
select b;
var results = qry.Take(10).ToArray();
blogList.DataSource = results;
blogList.DataBind();
}
The benefit here is modeling your data in one place and gaining both the data storage and the data access API. The additional benefit is that since the “M” file is just text it is easier to manage with source control. Change management of the file will be just like any other code you write. Merging need not be messy as it is with most graphical designers for database schema.
Where we are...
SQL Server Modeling represents a new way to think about build data storage layers in your .NET projects. It introduces some different workflows that will be uncomfortable at first but in the long term being able to develop the database and data access at the same time and refactoring it along with the rest of the code should make it simpler to develop real solutions.
In addition, by utilizing the “M” language to build your models (instead of SQL), you can be more productive in building your data models. Using “M” also allows you to build both the structure (or schema) of your data model as well as reference and test data. SQL Server Modeling Services makes the design of data models first class citizens of the platform.
