Designing an airline passenger reservation system

A question frequently asked on community sites such as stackoverflow.com and in the MSDN forums is “how do I generate DDL scripts with model changes“. Translated: some people want to use a model first approach with the Linq-to-SQL designer or the Entity Framework designer as the modelling tool.

This is a nice idea that I absolutely subscribe too. However, a modelling tool should be able to accurately describe the entire (as in all aspects of) the model so using either of those designers to model a database would result in schemas with no indexes besides primary keys etc. Support for incremental changes is also a pretty basic requirement in my opinion.

“Model First” in Entity Framework

A while back, the EF team published an article in their EFDesign blog where they described the approach they are taking in the next version of the EF designer, slated for release sometime around 2010 if I have interpreted everything correctly. They will add a new feature that generates the SSDL description of the storage layer automatically from the conceptual layer and then generate SQL DDL from that. Effectively they are throwing away the advantages that a layered model brings, and turning it into a 1:1 model no different from L2S models. Or in the words of Microsoft PM Noam Ben Ami: “We would like users to understand that this feature will regenerate all SSDL and all MSL from scratch.”

To add insult to injury, they won’t even support generating diff-scripts, e.g. if you add “EmailAddress” to your employee table they will re-generate the entire database rather than just issue a simple “alter table employee add email_address nvarchar(100);” statement. Again in the words of Microsoft’s “Noam Ben Ami” in the efdesign blog article: “your database will be recreated from scratch“.

This “regenerate the database” feature was demonstrated by another Microsoft PM, Tim Mallalieu, at PDC during his “Entity Framework Futures” presentation. (41:00 until 47:00). He also explicitly pointed out that the database will be dropped and re-created instead of amended with the changes (45:55 - 46:15 into the presentation) and added a gleeful “I told you so, when your data is gone“. Watch it here: http://channel9.msdn.com/pdc2008/TL20/

Not to mention indexes, alias types, views, etc etc. Belongs in the model in my opinion, does not according to Microsoft’s EF team / DP group.

“Model First” in Linq to SQL

Linq-to-SQL is not really designed for a model-first approach either. Unlike EFv1 however it has the ability to generate a database from scratch. Still no support for indexes etc in the model though, but it is nice to get a baseline database (tables, PKs, FKs etc) generated.

A lot of development time involves the usual changes to models and databases. Add a field here, drop a field there. Add a couple of new tables, a couple of new foreign keys etc. Who want to throw away the database and start with a new blank database every time some small change is made to the model? Not me. So I normally model my databases using modelling tools (currently Visio for Enterprise Architects 2003) that support forward/reverse engineering and change scripts, and then update the L2S model to correspond with the db schema. Supporting that scenario is the core reason why I wrote the “sync model” feature in Huagati DBML/EDMX Tools in the first place; I was bored with having to apply the same changes to more than one model.

Now I want to simplify that by generating basic change scripts; added columns, added tables, dropped tables, dropped/added foreign keys, etc as SQL DDL scripts. So I added that feature to the add-in. It is available in version 1.47 that was just released and is available for download now.

Demo

Allow me a quick demonstration using the Microsoft Northwind database:

First I open up my existing Northwind DBML test project that contain the baseline Northwind model. I add a new member/column EmployeeEmail to the Employee entity/table, two new entities/tables “EmployeeFamily” and “EmployeeRelativeKind” and associations between Employee/EmployeeFamily and EmployeeFamily/EmployeeRelativeKind.

Next, I pop over to the DBML/EDMX Tools menu in Visual Studio and select the “Generate DDL with Model <-> db differences” option.

And when the add-in has completed comparing the model to the underlying database it pops up a new SQL-DDL change script outlining the differences between the model and the database.

So, there you have it. This is available today for Linq-to-SQL. It generates change scripts with just the differences instead of “blowing away the database” (to paraphrase Tim Mallalieu). Huagati DBML Tools now support both forward and reverse updating between model and database.

I may even add the same feature for Entity Framework if there is enough demand for it so people can reverse/forward update their models without blowing away the existing SSDL/MSL and without dropping the entire database.

Ps. Dear Microsoft: if you’re interested in providing these features for L2S and/or EF to your customers so they don’t have to buy the add-in from me - you got my phone number.