Database design made easier

Boring stuff about me

I am a Software Architect in the Visual Studio group. I am interested in leveraging metadata to improve Microsoft’s servers, development tools and management tools. This has caused me to write a lot of T-SQL code, which has made me interested in making it easier to build SQL Server, based applications. If you write T-SQL for a living then I not only feel your pain but I want to help.

 

Prior to joining the Visual Studio group, I worked at Visio as Director of Database and Software Modeling Solutions. I have also been the Vice President of Engineering at Infomodelers (a database modeling company) and the development manager for the Visual Studio Enterprise and Visual Studio Team Edition for Software Architects products. I hope to share some of the insights I have learnt and, in turn, learn some more from you.

 

Not so boring stuff

I thought a good topic for this blog would be improving the SQL Server development experience. Compared with the development experience for, say, C# and Visual Basic the T-SQL development experience is terrible—no intellisense, refactoring, unit testing, code coverage or even good error reporting. Yet many developers spend large amounts of time building T-SQL code. SQL Server comes with a great set of tools for DBAs and for operational management but the T-SQL development experience is not even close to what I get with C#.

 

I see the problem as three fold: many developers do not have the training to use databases effectively, typical database development processes are poor and database development tools are weak. By enhancing the Visual Studio Team System product, I think Microsoft can help with all three issues. Our biggest challenge is that we write servers and applications in C, C++ and C# for a living, which makes it hard to understand what the T-SQL developer really needs. Nevertheless, it can be done it just requires attention to detail and tenacity.

 

By providing the right set of tools integrated with our recommended development practices, we can make T-SQL development faster and easier. However, what are the right set of tools and practices? This is where I am hoping that you can give me feedback and advice. I write T-SQL for prototypes and the occasional tool but I do not build databases for a living so my ideas are little more than guesswork based on working with customers and all the market research Microsoft does.

 

T-SQL development can be divided into two main areas: defining the declarative part (e.g. tables, foreign keys, views, …) and defining the imperative part (stored procedures, triggers, …). I think the declarative part is the most critical for correctness and performance. A good table and index structure will minimize the cost of reading data, updating data and the likelihood that the imperative part will contain an error.

 

Many data architects and my secret sauce for getting the design right is ORM (Object Role Modeling, see https://www.orm.net/) which is a conceptual modeling notation and method. It helps you specify database requirements in a way that end users can easily validate. From an ORM model, you can generate a good first cut declarative design.

ORM will not save you from beginner errors like using a social security number as an ID but it will help you to get the right data in the right place (surrogate keys are such an important topic I plan to spend a blog entry on them). The paper https://www.orm.net/pdf/dppd.pdf gives a brief introduction to ORM and https://www.orm.net/pdf/springer.pdf gives a more detailed introduction to ORM.

 

Various ER (Entity Relationship Modeling, see https://www.utexas.edu/its/windows/database/datamodeling/dm/erintro.html) variants are the main competitors to ORM. ER has the advantage of being reminiscent of tables, columns and foreign keys, which makes many people more comfortable with the notation. However, I think the presence of attributes prevents people from clearly thinking about the fundamental objects that need to be represented in the database and how those objects are related.

 

Over the years, there has been a convergence of ORM and ER so that modern ERs (i.e. those found in academic papers) are very ORM like and ORM supports summarizing relationships as attributes. Thus if you do not like the ORM notation you can use your favorite modern ER together with the ORM method to quickly design high quality databases.

 

Shameless advertisement

Over the last few years, I have been heavily involved with the DSL tools effort (https://lab.msdn.microsoft.com/teamsystem/Workshop/DSLTools/) which grew out of our experience with UML and building commercial development tools. Even if you are not interested in building your own development tools, the DSL team’s web site and blogs, provide a great insight into how commercial tools are built and how Microsoft hopes to improve customer involvement in building products.

 

This posting is provided "AS IS" with no warranties, and confers no rights.