Code-First Database Design Isn't

Let's talk about abstraction for a minute. Abstraction is a technique for making a complex thing simple, or at least simpler. It allows you to use something without having to be familiar with all the details of how it works. It provides a way to make changes to the implementation without the user - whether that's a human, an HTTP client, or the calling method - having to adjust how they're interacting with it.

Abstraction is not however a way to turn one thing into another thing. It is not an invitation to force a square peg into a round hole.

One of the most familiar interfaces in the world is the driver controls in a car: the steering wheel, the pedals, and the gearshift. There are many different engine designs out there, but they all use the same driver interface. The vast majority of drivers don't know the details of the engine, and couldn't care less: they have a useable and portable interface, and that's all that matters. Even when the engine type changes radically - say from an internal combustion engine to an electric motor - the interface stays the same. This is a beautiful example of abstraction.

But this abstraction can't work for everything. When we transitioned from horses to cars, the horse interface (reigns and verbal commands) simply wouldn't work, so a new interface had to be created. The car abstraction is too simplistic to be used in semi trucks - it's close, but more control and feedback is needed. Trying to drive a horse with a car interface would be probably harm the horse, and trying to drive a semi with a car interface would probably result in a wreck.

Microsoft's Entity Framework (EF) is a perfectly serviceable ORM / database client, and provides a good abstraction for running queries and turning them into objects. But it also has lot of functionality for a code-first database approach - i.e., creating class structures in order to define database tables. I feel this is a place where abstraction is being used to force a square peg into a round hole, because database tables and objects are different enough that the abstraction causes more harm than good, when it comes to relational databases.

One of the most common data types in object-oriented programming languages is "string". It's rare to see a class without at least one. But here's the thing: relational databases don't really have a concept of "string". They have a concept of "bounded character array", and a concept of "text BLOB (Binary Large OBject)", but not "string". The way these two database data types are handled in terms of storage and querying are very different from each other, and of the two of them, text BLOBs are less compatible, less efficient, and less flexible. This matters because it's common to see EF data models that just use "string" without any additional constraints, which means the database will treat it as a text BLOB. This way lies poor performance.

This is not especially difficult to remedy on a column-by-column basis: if you decorate the property with a [MaxLength] attribute, then EF will begin to treat it as a bounded character array, and the database engine will be much happier. But that's just one of the many pitfalls we run into trying to define database tables this way. 

Let me highlight another common one. A database table wants to have a primary / clustered key that is sequential - it's the most efficient way to store data in this mechanism. Yet a lot of EF models choose a GUID/UUID as their primary key. GUIDs are generally not sequential, and they're also much larger than most tables need. Even very large tables typically only need a 64-bit integer for their key, so a 128-bit GUID is twice as big as needed. Those extra, 'wasted' 8 bytes on row after row can add up to a lot of unnecessary storage. GUID-key tables also have very fragmented storage layouts, leading to further performance issues.

Again, this is not a hard problem to fix - you just use an integer (or short, or long) instead of a GUID. But these examples, and many others we could cite, tell us that database tables needed to be designed with the database in mind. By letting the code define them, EF is trying to abstract away too much about the database. It's providing an interface that wants you treat relational database tables as collections of objects, which is not something they're built for. There's nothing wrong with object stores, but if that's what you want/need, then use MongoDB, not EF on top of Postgres or MySQL or SQL Server.

It's like I keep saying: deal with things as they are, and don't try to make them into something else.

"But programmers don't deal with tables," you say, "they deal with objects. We want those details to be abstracted away." Again, if that's true, you probably want Mongo, not a SQL database. But it's not like EF models and well-designed tables are incompatible: it's just that those tables have to built for the database, and then the models created to mirror them. A code-first database design approach isn't actually designing database tables - it's designing objects, and tables are not object collections. In other words, have a DBA or DB engineer design the tables, and then have the programmers consume them. (These needn't be two different teams, or even two different people, just saying you have to think about the two solutions each on their own terms.)

Code-first is a great way to define what an application needs from the database, but it should not be used to define the database itself. A database table needs to be defined according to the conventions, needs, and best practices of a database, not code objects, and using a code object to define a table is a poor approach.