MSchema and Decorator Tables

Url: http://social.msdn.microsoft.com/Forums/en-US/o…

When I first grabbed the Oslo SDK, I wanted to first dive into MSchema.  MSchema is a language for defining your data store and relationships between data that Oslo uses to define how to handle storage.  My first attempt was to try and replicate the store model of my VideoGameStore data that i’ve been using to show off Silverlight and ADO.NET Data Services. My original attempt was:

module VideoGameStore
{
    type Product
    {
        ProductId : Integer32 = AutoNumber();
        ProductName : Text where value.Count() < 250;
        Description : Text#250;
        ReleaseDate : Date?;
    } where identity ProductId, unique ProductName;
    
    Products : Product*;
    
    type Game : Product
    {
      Genre : Text#100;
      Developer : Text#150;
      Publisher : Text#150;
    };
    
    Games : Game*;
}

This resulted in the non-decorator table solution of:

create table [VideoGameStore].[Products]
(
  [ProductId] int not null identity,
  [Description] nvarchar(250) not null,
  [ProductName] nvarchar(249) not null,
  [ReleaseDate] date null,
  constraint [PK_Products] primary key clustered ([ProductId]),
  constraint [Unique_Products_ProductName] unique ([ProductName])
);
go

create table [VideoGameStore].[Games]
(
  [ProductId] int not null identity,
  [Description] nvarchar(250) not null,
  [Developer] nvarchar(150) not null,
  [Genre] nvarchar(100) not null,
  [ProductName] nvarchar(249) not null,
  [Publisher] nvarchar(150) not null,
  [ReleaseDate] date null,
  constraint [PK_Games] primary key clustered ([ProductId]),
  constraint [Unique_Games_ProductName] unique ([ProductName])
);
go

The reason this didn’t work is that (curly braces aside), MSchema isn’t  talking inheritance (as I saw it). In my model I specify that the type “Game” is ‘derived’ from “Product” which feels like inheritance but is actually extended the type, which is why the resulting SQL results in two tables with overlapping data.

What I should have done was:

module VideoGameStore
{
    type Product
    {
        ProductId : Integer32 = AutoNumber();
        ProductName : Text where value.Count() < 250;
        Description : Text#250;
        ReleaseDate : Date?;
    } where identity ProductId, unique ProductName;
    
    Products : Product*;
    
    type Game
    {
      Product : Product;
      Genre : Text#100;
      Developer : Text#150;
      Publisher : Text#150;
    } where identity Product, Product in Products;
    
    Games : Game*;
}

In this case I am creating a type that encompasses the Product type so in this case by including the Product in the type, I am creating that extension that wanted. By making this change I get a different database schema generated:

create table [VideoGameStore].[Products]
(
  [ProductId] int not null identity,
  [Description] nvarchar(250) not null,
  [ProductName] nvarchar(249) not null,
  [ReleaseDate] date null,
  constraint [PK_Products] primary key clustered ([ProductId]),
  constraint [Unique_Products_ProductName] unique ([ProductName])
);
go

create table [VideoGameStore].[Games]
(
  [Product] int not null,
  [Developer] nvarchar(150) not null,
  [Genre] nvarchar(100) not null,
  [Publisher] nvarchar(150) not null,
  constraint [PK_Games] primary key clustered ([Product]),
  constraint [FK_Games_Product_VideoGameStore_Products] 

    foreign key ([Product]) 

    references [VideoGameStore].[Products] ([ProductId])
);
go

So my new Games table truly extends the Product table (as a decorator table). I have a reference to the Product in the new table (represented by the Product value which is a foreign key to the Product table).

My problem is that MSchema (and likely with the rest of the M family of languages) is that they feel like curly-braced languages (e.g. C#, C++, C, Java) so that when I am writing M I get caught up in trying to apply concepts from that world to this very different world.

At least the guys on the Oslo forum helped sort me out…