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.

Back in March when I was testing VS2010’s final versions, I tried to get the contents of a dialog box in order to look up an error.  It’s a small thing, but you can’t copy the contents of dialog boxes any more with the mouse.  I was bummed, so I submitted a Connect ticket:

“It is not possible to select the text in most dialog boxes generated for Visual Studio exceptions. For instance, when attempting to change the network mix in Web Load Tests, the dialog box refers you to a URL at go.microsoft.com for details on administration privileges required. However, it is not possible to select the URL and paste it into a browser.”

As I expected, I was told to go away, they were in RC and weren’t going to add a feature now.  That’s cool.

However, I just got an email from Neelesh on the Load Test team, and he points out that:

“As a workaround you can use "Ctrl_Insert" to copy message box text, paste in notepad and select URL. Kludgy workaround, i agree.”

Kludgy or not, it works great and I’ll use it.

Since I have been back on the consulting bandwagon, around business types and not the same people every day, I have gotten a lot of questions about the tech that I carry every day.  I promised a few people I would blog about it, so here we are.

Here is my day to day tech.

The big laptop is a Toshiba Tecra M7.  It is the best laptop I have ever owned.  It’s it a tablet, and generally rocks.  However, Toshiba won’t support Windows 7 on it, so it runs too hot and won’t wake up properly from hibernation.  Considering going to Windows Server.

The e-reader to it’s left is a nook.  The nook is the best overall e-reader on the market.  It has a soft keyboard, and flexible, Android-based display.  The whole OS and rendering system can be replaced with a Micro-SD card.  Barnes and Noble does a great job supporting it.  It might not have been ready for market when they launched it, but it was always the best out there.

Above that is my Texas Instruments Chronos ez430.  It is a programmable watch.  It has a MSP340 microprocessor, and comes with a wireless interface and pinning for a usb adapter.  You can do neat stuff like change your PowerPoint slides, or measure your sensei’s punch speed with it.

To it’s right is my IronKey.  This is a USB drive, 4 gig, that is waterproof and hardware encrypted.  If you fail to enter your password 10 times, it destroys itself.

Next is the Nexus One.  This is by far the best device, let alone phone, that I have even owned.  It is a Android based slate similar in form factor to an iPhone, but I think it has a lot less suck.  (I know everyone loves the iPhone, it’s like a puppy.  I think it is unusable.)

Hmm, what’s next.  Oh, my Wand Of Business Analysis +4, otherwise known as a livescribe pen.  Basically, everything I write on the special books that I get for it gets moves to my laptop for later analysis.  Also, everything that is said while I am writing is recorded, in time with the writing.  So if I need to know what a customer said while I drew that diagram, I just click on it and the recording starts there.

Finally, a walkman.  Yes, I said a Walkman.  It’s a 8 gig Sony Walkman MP3 player.  Why?  Lots of reasons.  I don’t like to kill my phone battery running music.  I use it as a radio.  I can feed the music to my car.  I have it sync to my desktop to get podcasts.  And it is light, cheap, and if I destroy it by accident I can get a new one with my Best Buy Silver Reward points.

So there we have it.  Years of geeky research and gadget dependency reduced to a blog post.

Anyone who has read my blog or twitter feed, or worked with me, or drank with me, or been in the same room with me for longer that ten minutes, all know that I do not approve of using Access as a business-class development platform.  The technical debt that it creates is not worth the effort, and you end up depending on a software package that is better suited to tracking your recipes than your HR paperwork.

That said, there are some solutions that are well suited for Access, and one of them is form-filling.  In this example, we have an HR department that is required to fill out a form that uses some SQL Server-accessible data, and some entered data.  The resultant paper form has to match a template exactly.

This is the kind of solution that VSTO is actually very good for now, but VSTO wasn’t a reality when the solution was developed, so I give them a break there.  Since the only other real option is to build out a full windows application just to print one form, or to save off copies of Word documents, Access is a decent solution here.

Anyway, back to the problem at hand.  I need to add some fields.  The application is an Access 2007 ADP upgraded from Access 2003, and uses data from a SQL Server 2005 database.  I shift-double-click to enter the editing form.  As I right click on the table to enter design view, I get a surprising error:

If you can’t read that, the text says:

"Table 'table_name(dbo)' could not be loaded.

The table being loaded into memory has a user-defined data type ('bigint') that is not recognized.

Close all your open database diagram and table designer windows.  The new data type will be recognized when you re-open the diagram or table designer."

Needless to say, when I close all open windows and re-open them, the problem is still there.

So, usually when I blog about a problem, I have a cool fix.  This time, not the case.  I posted to the partner support forums and got this response:

“Based on my test, I was able to reproduce the issue on my side, if I create a table in SQL directly and open the ADP file associated with the database I see exact same error message when I try to design the table in Access.

create table a1 (id1 bigint primary key)

Also, if I try to create a new table from Access, I cannot find "bigint" in Datatype options.

It seems to be a limitation or issue in Access that it doesn't support bigint in design view though the tables work as expected in other functions.”

That’s kind of a shock: it’s actually a bug in Access 2007.

Anyway, I ended up dropping the table and re-importing from SQL Server which worked fine, BigInt and all.  I have a response in to them as far as finding a better solution, and I wonder if using an ALTER TABLE query might work.  Maybe I’ll roll back and try that.  I’ll post any update here.

Thanks to Mike Wood and others for asking me to give my SQL Modeling talk at CODODN.  Events like CODODN are important, because they bridge the gab between local events and the larger regionals like CodeMash.  Smaller groups sometimes mean better hallway conversations and the like.  Kudos to all those who participated in getting this together.

Anyway, here is the solution (WarehouseManagementSystem.zip (2.65 mb)) from my talk.  No slides for this talk, just a little talking and a lot of coding.  Get the bits from the SQL Modeling Website, and make sure you have SQL Express 2008 installed.

 Thanks to all who attended; good questions and insight.