It's Cleanup Time, Entity Data Model Style

By Martin Schaeferle

As good and useful as a model generated using database-first design can be-it is highly dependent on the quality of the underlying database design-you'll almost always need to do at least small tweaks to the resulting Entity Data Model. There are some things that Entity Framework just can't handle intelligently, such as naming conflicts, vagaries in English pluralization, and various other issues that make the model usable but less than ideal for development work.

Similarly, there are tweaks you can make that make the data entities easier to use, such as setting intelligent default values for properties when that makes sense. And many of these techniques apply to creating a model from scratch using model-first design, so you're likely to put them to use no matter how the model comes into existence.

This article focuses on cleaning up an Entity Data Model once it's created.

One of the very first things you should do after creating a model is to clean up the names of objects and properties in the model, making sure that they make sense for applications that use the model. Just because a name made sense to the database designer doesn't mean that it will make sense to application developers. Make sure that the terminology is correct, consistent, and valid. Good names can make a huge difference to the productivity of application developers, particularly when they don't constantly have to stop and think about which inconsistent name is used in the part of the database they are working with at the time.

One big reason you'll have to rename things in the model is if the person who designed the database used outdated or misguided naming standards. For example, the figure below shows just one table from a very large database that we've encountered in our consulting work. In fairness, the original database was created back in the days of SQL Server 2000 before schemas were available as a kind of namespace container.

So all the tables have a "tbl" prefix on the name, followed by the name of the functional area ("Subscript" here for the subscription area), and the table name ("Member"). Complicating things further, some of the tables have a three-character subscript on the name ("smi" in the figure), none of which any current employees of the company has any idea the meaning of.

Naming standards implemented in a production database.

The fields in all the tables follow a similar pattern, with a data type prefix, a functional area name (no one knows why the table in the above figure uses both "Member" and "Subscript" in various field names), and finally the field name. Unfortunately, the data type prefixes are unreliable because over the years it has been necessary to change data types here and there-which isn't necessarily a breaking change-without changing the field name-which is a breaking change.

The nightmare of this database design has persisted for years because the company has a large, complex Web site built on it, as well as various support applications and tools. Making all the required changes to the applications and stored procedures would be a nightmare if any field names changed. As you can imagine, we had to spend a ton of time cleaning up the names in the Entity Data Model we created for the database. It was time well spent, however, because we were able to simplify the names, clean up inconsistencies, and change names that made no sense at all. All without changing the underlying database design.

Fortunately, all of the AdventureWorks databases use a much saner naming convention, so there isn't nearly as much work to do. But there are still things to refine that will make the model much easier to use in an application. And with Entity Framework version 4 and later, the Entity Data Model Wizard has already made entity and entity set names singular and plural by default, which saves a lot of the work required in earlier versions. It isn't perfect, but it is good enough by far and away most of the time.

You can fix database object misspellings in the model as well. Sometimes you can't change the misspellings because of application dependencies, but you don't have to perpetuate the problem in your new model and applications.



Martin Schaeferle

Martin Schaeferle has taught IT professionals nationwide to develop applications using Visual Basic, Microsoft SQL Server, ASP, and XML. He has been a featured speaker at Microsoft Tech-Ed and the Microsoft NCD Channel Summit, and he specializes in developing Visual Basic database applications, COM-based components, and ASP-based Web sites. In addition to writing and presenting technical training content, Martin is also LearnNowOnline's vice president of technology.

This blog entry was originally posted September 24, 2012 by Martin Schaeferle

Entity Framework