Url: http://wilderminds.blob.core.windows.net/downloads/MappingRelation…

I’ve been playing a lot with M to do my database and test data loads for some internal projects for AgiliTrain and I’ve noticed an interesting representation for how relationships in MSchema are generating their SQL. In MSchema you have a couple of ways to represent a relationship between tables. The relationship syntax is one feature I really like in MSchema.  For example, let’s take the Northwind database’s Customers and Orders tables. In MSchema I can define them in a traditional way where the Order has a reference to the Customer table:

module Northwind
{
  type Customer
  {
    CustomerID : Text#5;
    CompanyName : Text#40;
    ContactName : (Text#30)?;
    ContactTitle : (Text#30)?;
    Address : (Text#60)?;
    City : (Text#15)?;
    Region : (Text#15)?;
    PostalCode : (Text#10)?;
    Country : (Text#15)?;
    Phone : (Text#24)?;
    Fax : (Text#24)?;
  } where identity CustomerID;
  
  type Order 
  {
    OrderID : Integer32 = AutoNumber();
    OrderDate : (DateTime)?;
    RequiredDate : (DateTime)?;
    ShippedDate : (DateTime)?;
    Freight : (Decimal9)?;
    ShipName : (Text#40)?;
    ShipAddress : (Text#60)?;
    ShipCity : (Text#15)?;
    ShipRegion : (Text#15)?;
    ShipPostalCode : (Text#10)?;
    ShipCountry : (Text#15)?;
    CustomerID : Customer? where value in Customers;
  } where identity OrderID;
  
  Customers : Customer*;
  Orders : Order*;
}

```csharp

If you notice the **CustomerID** in the **Order** type, you can see that we are defining relationship in the type so that the database construction is very predictable. For example, an ERD of the database created looks like so:

![Simple Relationships](http://wilderminds.blob.core.windows.net/img/mschema2.png)

My problem with this strategy is that when you define **MGraph** representations of the data, you have to define your objects separately. **MGraph** lets you assign names to do this so its pretty easy:


```csharp
  Customers
  {
    BobsCrabShack {
      CustomerID = "BOBSH",
      CompanyName = "Bob's Crab Shack",
    }
  };
  
  Orders 
  {
    {
      OrderDate = 2009-01-01,
      Freight = 199.00,
      CustomerID = Customers.BobsCrabShack
    }
  }

Notice that the MGraph uses the alias set up in the customer to assign it to the Order. This way that MGraph (or testing data in my case) does not need to ever know about generated ID’s.  The SQL generation in the M Executor (mx.exe) takes care of that for us. There is a fundamental problem with this though.

For several situations (MGrammar’s AST to MGraph for example), i’d like to nest the objects to imply that owner ship.  For example, I’d like to craft my MGraph like this:

  Customers
  {
    BobsCrabShack {
      CustomerID = "BOBSH",
      CompanyName = "Bob's Crab Shack",
      Orders = 
      {
        {
          OrderDate = 2009-01-01,
          Freight = 199.00
        }
      }
    }
  };

You should notice that the orders for Bob’s Crab Shack are nested inside the Customer.  When you’re dealing with a tree of information, constructing the MGraph would be easier in this way. But this requires we look at the relationship differently in MSchema:

module Northwind
{
  type Customer
  {
    CustomerID : Text#5;
    CompanyName : Text#40;
    ContactName : (Text#30)?;
    ContactTitle : (Text#30)?;
    Address : (Text#60)?;
    City : (Text#15)?;
    Region : (Text#15)?;
    PostalCode : (Text#10)?;
    Country : (Text#15)?;
    Phone : (Text#24)?;
    Fax : (Text#24)?;
    Orders : Order*;
  } where identity CustomerID;
  
  type Order 
  {
    OrderID : Integer32 = AutoNumber();
    OrderDate : (DateTime)?;
    RequiredDate : (DateTime)?;
    ShippedDate : (DateTime)?;
    Freight : (Decimal9)?;
    ShipName : (Text#40)?;
    ShipAddress : (Text#60)?;
    ShipCity : (Text#15)?;
    ShipRegion : (Text#15)?;
    ShipPostalCode : (Text#10)?;
    ShipCountry : (Text#15)?;
  } where identity OrderID;
  
  Customers : Customer* where item.Orders <= Orders;
  Orders : Order*;
}

Two things to notice in the MSchema example, the relationship is defined in the parent not the child. The Customer type now has a property called Orders that is of type “Order*”. So that the Customer is expected to have a list of orders. In the Customers extent, we create the constraint to say that each Order in the Orders property is in the Orders extent. (A bit confused by the identical naming but I think you can make sense of it).

So that is all good, right?  Well, it is almost fine. The schema generation of this pattern is decidedly different:

Note that the relationship is now represented by a mapping table between the Orders and Customers. At first I thought this was just a mistake, but in fact its the right thing to do.  Why?  Because the MSchema language has no way of us telling it that the Orders are a type that are naturally ‘owned’ but Customers.  In other words, Orders will have one and only one parent: Customers. Since MSchema can’t describe that, it does the right thing and use a mapping table to establish the relationship so that other objects that contain Orders can establish their own relationship.

I’d love to have a way to have my cake and eat it too. If there was a way to specify that we were the one and only owner of a particular relationship, then it could create the SQL in a smarter fashion, but that feels like its trying to be too smart and might cause confusion.

If you want to play with the two styles, I’ve included a .zip file (linked above) with the two “M” files.

What do you think?