Code-First or Database-First, how to choose?

Question

Let us suppose we are going to start new project - application that contains some business logic, user interface on ASP.NET, WPF or both of them. We'd like to use ORM or DAL code generator and implement our business logic in .NET classes. There are several fundamental ways how we can express our ideas of business domain:

• Implement business classes on .NET and let ORM generate appropriate database schema
• Create database schema manually and generate .NET classes by code generator
• Use some kind of visual designer, that can generate business classes and database structure or script

What do you prefer to write: "Create Table Persons ( ... )" or "public class Person { ... }"?
What are Pros and Cons of those ways?
Maybe there are some special situations where one way is better than another?
How to choose optimal way in a particular project?

I am quite familiar with "Code-First" (or "Model-First") way, but it seems most of ORMs are designed as code generators or mappers, that suppose that I will manually implement both database structure and business classes.

Answers based on expirience and examples of ORM's are especially welcome.

Edit: Note, the question is not "What should I do first when starting new project?", but "What should be manually declared / automatically generated, domain classes or database structure?"

Acarpous

Darned good question! +1 – Empress

Near duplicate question #274590 – Tridimensional

Answer 1

I think the appropriate approach to system analysis and design is to start by modeling your objects and the relations between them first. If you're creating a library system you should think of the phrases Book, Author, Publisher, ISBN as objects not as database tables or attributes. I believe this is the way it should be. That been said, let's admit that code generators save way a lot of time, and those require a relational database in order to generate the model and map it to the DB objects. I think this is the major reason why developers tend to start by the D.B. What could prove my point more is that code generators developers is trying hard to reverse the currently implemented operation (i.e. You provide a business model-objects and classes- and the generator creates the DB with the appropriate schema for this).

Edit:
Here's an example of domain-first generators (ADO.NET Entity Framework itself) Model First :

Visual Studio 2010 has to ability to generate a DDL and create a database to store the entity data model. The developer has complete control over the entire process being able to customize the DDL, or to select the database he desires, or fine tune the mapping process.

Glauce

Well, they don't necessarily require a database first, but they require an object-relational model of some kind. By creating the database first, you kill both birds with one stone instead of creating a primary artifact that serves no other purpose in the project. FWIW, a LINQ to SQL DBML file could serve as that artifact, since you can directly generate the database from it and you can also target it with a code generator. – Louielouis

Great quote - implicitly concedes the (IMHO) preferred process of omitting the overhead so that "The developer has complete control over the entire process being able to write the DDL, or to select the database he desires, designing and developing the mapping process." – Tridimensional

Answer 2

Why not interface-first?

Too many apps start with a program-first mentality. That's a bad idea. Programming is the heaviest component of building an app, meaning it's the most expensive and hardest to change. Instead, start by designing first.

Design is relatively light. A paper sketch is cheap and easy to change. html designs are still relatively simple to modify (or throw out). That's not true of programming. Designing first keeps you flexible. Programming first fences you in and sets you up for additional costs.

This is from Chapter 9 of Getting Real by 37signals.

Hillie

Concerning to our question: Even if we start with designing UI, we'll have to express our ideas on domain model in business classes or database structure. – Acarpous

Answer 3

“Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.”

— Fred Brooks in “The Mythical Man-Month”

Phytophagous

MMM is about an operating system, which requires no UI. – Tridimensional

MMM is about the ideas Brooks observed while managing the development of an operating system. The ideas not only applies to OS development. – Lody

There are two important parts of your app, the data model and the business rules. The business rules are going to be neither structural, object-oriented, nor normalized. Make a nice schema that fits with how the DB can manipulate data, and write the rules with whatever works nicely. – Monoclinous

I'll second that. I might be a bit 'oldfashioned' but DB is everything! The rest can simply fit. – Inductance

Answer 4

In my opinion there is no correct answer to this. I guess it mostly boils down to your own personal or your teams preferences. All mentioned approaches (database first, code first, interface first) have their own advantages and disadvantages.

I'd probably sit down with pen and paper and sketch up the general structure and the main functions of the application before i do anything specific, be it code or database tables. A simple drawing of the basic user interface also helps a lot.

Halfwitted

Certainly before developing database or domain classes you can draw interface, think about model structure and so on. The question is what is primary thing, domain model or database. – Acarpous

What you've strangely neglected is the user. Why not start with User Stories? – Tridimensional

I think the assumption is that user stories are already defined, and have exposed requirements. The question is more specifically about how to implement those requirements... – Epidemiology

@AlexKofman In my opinion, database itself should be last. Database is just a way of storing away stuff, it's not a way in itself to model real life entities. That said, a domain model will often map neatly into an RDBMS. – Halfwitted

Answer 5

Analysis of requirements first, and then some documentation of those needs and an overview of the data aspects of this?

Then you know what data you'll be capturing and how it relates to other data, and can design a database schema or data structure to match it (as logical objects/tables of related content, not "tab1_data", "tab2_data" matching the data capture process which could change, but you know that!). You could even design a .xsd first and generate code and a database schema from that. It's all fun and games these days, depending on your skillset.

As the database schema in my mind stores the data, and that is the really important thing for a business to have, I would design that first - multiple tools may access it in time, maybe the original system would be replaced down the line (e.g., migration to newer tools/languages/interfaces). If you know nothing about database theory, then maybe that's not your best option but I would still get any generated schema verified by someone else.

Professed

+1 good point! Data usually lives longer than apps, so making sure the data structures in your database are 150% correct and sound is more important than app design, IMHO. – Litt

Answer 6

In most cases it won't matter much. It is more up to personal preference and skill than anything else. Most apps are not going to suffer much either way, use whatever your team is comfortable with. Where the choice really matters it should be obvious which approach to go for.

That said, my personal opinion is that "database first" is generally the safer choice. If the data is in any way important, especially if it is important outside the scope of your particular app, you want to have full control over how it is stored.

"Code first" (implied: leaving the database in the hands of some automatic tool) is in my mind really a shortcut, one you should use when (and only when) you know for sure you can get away with it.

Allowedly

Answer 7

To answer your edited question (manual db/auto classes or manual classes/db), I'd choose "neither". Autogenerated code of both kinds are to be avoided for a number of reasons, first of all YAGNI. You end up with code you never wrote but are nonetheless responsible for, code you'll never use, and (in my experience) code you'll end up spending more time refactoring than if you'd designed and written it yourself in the first place. And they both keep your focus far away from the most important location - the User.

Tridimensional

Answer 8

Start by not directly thinking about either, rather "model" (preferably on paper) what parts your application will have from the users point of view.

If you have a clear mental picture of that model, You can divide the parts up in common and specific little elements which you can translate to both object definitions and database tables.

I find this method to cut database normalization time and effort significantly.

Nucleoplasm

Answer 9

Domain modelling vs relational modelling

There are several fundamental ways how we can express our ideas of business domain

I think it is important to first establish that the domain is independent of concrete technologies and/or programming paradigms (e.g. OO, FP, relational). This answer will assume you've already separately defined your domain, e.g. using DDD practices, and now wish to use a relational database to store it.

Relational model strengths

The relational model was invented, among other reasons, to do away with the many problems that were caused by previous models, including the networked model (which includes OO models), the hierarchical model (which includes XML/JSON models), or simple key value stores. It has a lot of strengths over all alternatives, which has made it so popular for decades.

In my opinion, these strengths indicate that you should design your database model inside of the database, which has been made for precisely that purpose. All other models, including your client model, are copies of that original, relational model from within the database. Thus, all other models should be derived from it, not source for it.

See the relational model, expressed in DDL, as your source of truth

XSD is the same

XML/XSD is a similar technology. Your data is expressed in terms of XML, but when two systems communicate with each other through an XML based API, XSD (or e.g. WSDL if you want) is the most appropriate language to specify that communication.

If you want to to bind to your XML documents using client technology, e.g. JAXB in Java, then you should generate those JAXB classes from the XSD, not vice versa. Why? Because XSD is the source of truth

I've written about this topic here.

Silverpoint

Answer 10

Personally I like control over creating RDBMS objects. When I use the EF code first, it actually created more work for basic things such as table relations etc. which most decent code generators does for you out of the box ( I know thats the idea ... more control on your Models!). Also the Idea that EFCF will generate the db as I am hoping for is a bit scary. (traditionally Code evolves more easily then RDMBS!)

For a system which required constant evolving (for e.g. SaaS), and usually a large Enterprise Level system with 500 + tables etc, its can be less attrative proposition. On the other hand if you have a proper SQL Server 2008 database project with all the tables,SP,Triggers, Indexes scripted and you can deploy them from the Visual Studio it much more managble. You now have freedom of using any codegen you want for your tables (even build your own)

You are not tied to the on Framework (EFCF) you can then use different ORMs (NHibernet for e.g.) depending upon your 'mini' project requirement in your large system.

Saiff

Answer 11

There are 3 important aspects that need to be considered when developing a database application...

1. User Experience
2. Data Quality
3. Cost to maintain the first two (User Experience, and Data Quality)

I believe the priority of these three items are expressed in the order they are presented, meaning the highest priority is User Experience, the second highest priority is data quality, and the third is the cost to do so. Of course these can be debated, but the notion of code first or database first is relative to the third priority - the cost. Whatever the choice is - code first or database first, ensure the first two priorities are fulfilled...

Epidemiology