Modeling the ‘exactly one of several collections’ case in EF and M

For a long time, I have used the ‘totally unique’ power of the Guid to design the ‘exactly one of several collections’ problem in my databases. The Zen case is that of the container in an inventory management system.  The container can be on exactly one of the collection of stores, or exactly one of the collection of trucks, or exactly one of the collection of warehouse locations.  I can’t, in any case, be in two locations, or no location.

How I usually implement this is to have a single LocaationId in the Containers table that is of type GUID, and is a foreign key for the TruckId, StoreId or WarehouseLocationId.  Since Guids are globally unique I don’t have the worry about a duplication between tables.  Effectively, the Stores, Trucks and WarehouseLocations tables have an implicit uniqueness constraint, which I can enforce in code if I am getting really squirrely.

I was pleased to see that Entity Framework 4 handles this well.   A “Model from Database” command puts up an entity model that looks strikingly like our domain model diagram.

All I have to do here is alter the navigation properties to show a single location, but that pass back either a store, truck or warehouselocataion, based on maybe a simple Location interface.  Good enough.

Where I was curious is in learning how M will handle this design.  As it turns out, not as well. I ran the “Model from Database” and got this:

module dbo
{
    export WarehouseLocations, Stores, Containers, Trucks;
    WarehouseLocations : {({
        WarehouseLocationID : Guid;
        Aisle : Integer32;
        Shelf : Integer32;
    } where identity WarehouseLocationID)*};
    Stores : {({
        StoreId : Guid;
        StoreNumber : Text where value.Count() <= 16;
    } where identity StoreId)*};
    Containers : {({
        LocationId : {
            StoreId : Guid;
            StoreNumber : Text where value.Count() <= 16;
        } where value in Stores;
        ContainerId : Guid;
        Description : Text where value.Count() <= 128;
    } where identity ContainerId)*};
    Trucks : {({
        TruckId : Guid;
        VehicleCode : Text where value.Count() <= 16;
    } where identity TruckId)*};
}

Yeah, that wasn’t what I was looking for.  Somehow, the members of Store ended up as values of LocationID as an entity …. meh.  It’s CTP software, but it is worthwhile analysis.  Perhaps I’ll dig in and see if I can figure out how M came up with this after the weekend.