I’ve been brought on to work on a particular application in the past couple of months. I don’t mean to speak ill of it, because it really is a nice application that works very well. And it was made by people much smarter than me.

I try not to judge, since I wasn’t there when it was designed or written. However, I do think there are several lessons that can be extracted from it. I’m aiming to do a quick series of posts on such lessons.

In part one, I want to talk about how the ORM allowed the Database and Application designs to kind of meld into one another, and each was made far worse for it.

Don’t let your DB design steamroll your application design (or vice versa)

ORMs are great. They make interacting with databases super-easy. But they are dangerous. You need to understand the abstractions to be able to make proper use of them.

The ease of database interaction ORMs provide really cannot be overstated. But don’t let the shape of your relational data force ill-advised designs on your application. ORMs that generate models for you make this very easy. Resist at all costs.

I know this can sound like “all you need is C and SQL” dogma. And, perhaps, it is in some ways.

You need to have strong design on your database and your application. And, often, they are separate designs. Certainly, one can and should inform the other, but one should not override the other. If that happens, you are doing something wrong.

In the same way you don’t store data the same way in relational and noSQL databases, you probably aren’t going to want to pull your data out in exactly the same shape you are storing it.

Or maybe you do. Just give it some thought. Please.


(I’ve changed many pretty much all of the details for discussion’s sake, but the general design remains the same. Also, this particular issue has since been rectified, and the offending code has been deprecated. But it’s still a good illustration)

This application tracks the presence of chain restaurants in major cities. Most cities have one or more chains. Most chains have a presence in one or more cities.

When you view /restaurants/mcdonalds on the UI, it shows you a list of cities in which McDonald’s has a precence. Clicking on a city shows you the CityRestaurant, which is metadata about the number of locations that chain has in the city. When you view /cities/boston on the UI it shows you a list of chains that have a presence in Boston. Clicking on a chain shows you the CityRestaurant, which is metadata about the number of locations that chain has in Boston.

Simple, right?

Well it also has a RESTful RESTish RESTy JSON API. And things look very different from there.

The API has a /restaurants endpoint. It is used to construct the list of chains for the menu.

When you hit the /restaurants endpoint, it should return you a list of chains, right? Or maybe a list of chains and all of the cities each has a presence in?

Sort of.

They let the database design override the application design. And they “kept the code simple” by trusting the ORM. To an absurd degree.

The ORM in this case is Entity Framework, but this can happen to anyone. The code in the RestaurantRepository looks something like this:

return _repo.Restaurants.AsEnumerable().Select(x=>new Restaurant(_repo.GetCities(x.Name));  

The /restaurants endpoint pulls all of the Restaurant models out of the database. Since the Restaurant models in the database are based on the database design, each Restaurant contains an IEnumerable<City> for all of the cities in which it has a presence. Which seems reasonable. Buuuuuut…

Each of the City models contains an IEnumerable<CityRestaurant> with metadata on all of the chains that have a presence in that city.

So, in short, that list of 25 restaurant chains you wanted? With maybe a bit of metadata and links for more information?

Instead, you get a 15MB JSON document.

The best part is this. The menu also has a list of Cities. Makes sense, right? The user may want to navigate starting with either Cities or Restaurants.

As you might have guessed, the /cities endpoint does the exact same thing.

Both of these endpoints return these reams and reams of JSON. And they both get called. On every page. Essentially, the entire database gets pulled into the application constantly.

Make use of the ORM, to be sure. But be careful about what you’re doing.