Domain Models, Data Models, and How They Resemble Each Other

Dear Junior

In what ways domain models and data models should resemble each other is a really interesting topic. It is not uncommon for me to ask for what a “Foo” and a “Bar” really are and what their relationships are and upon that question being answered by the architect who with a smiling face show me the database schema, complete with join tables and everything. That was not what I had in mind.

So what is the difference between a database data model and a DDD domain model? Why do I care?

For me, it basically boils down to the purpose of the respective model. The purpose of the database data model is to provide the system with an efficient storage for persisting data, and for searching same data. The purpose of the domain model (a la Domain Driven Design) is to work as the focal point and a vehicle for communication about the system. In the database model you might have redundancy and pre-aggregated data for efficiency. In the domain model you try to make each concept and each relation as clear and unambiguous as possible.

True, in some places the database model is used as the domain model, and even the business side domain experts have learned to read the schemas. My experience is that even though this might work, for example by using ER-diagrams instead of the actual tables, it has its limitations. First, people tend to forget the distinction between the concepts per se, and their realisation in the database. So, if you want to re-conceptualise (which is good to try from time to time) you might be struck with counter arguments such as “but that will violate the constraints in the triggers”. Secondly, a database data model must by necessity cover every single piece of persistent data – however marginal its importance. To do strict modelling of a concept is a quite brain-expensive operation, thus it is simple waste of precious resources to do so on every minute detail. To get leverage out of your modelling efforts it usually suffices to concentrate on the most central parts. Thirdly, a database model will leave out data that is not persistent, or is extracted or imported from other sources - and those might well be very central to the conceptual understanding of the problem at hand.

For these reasons, I simply advice to do a separate domain model, even if you have a complete database model in place. They simply facilitate different kinds of discussions.

So, how much should these models resemble each other? Well, let us say that we have established that the domain model is the focal point of discussion. Further on, the domain model has grown out of a discussion where several types of input and considerations have been taken into account: how the customers of the product (e g end users) think, standards within the domain, GUI experts, and database experts. All those inputs are needed to give a model that fulfils its purpose – to enable us to build a good system. In that way the domain model is the result of a negotiation between several parties with the goal of meeting each party’s need as well as possible. So, as the database expert has his or her say in that discussion, there should be some level of resemblance.

But, how much should they resemble each other? Well, if the domain model is the common and central model, then each party will need to shrew it slightly to realise it into its context. The programmers might need to translate concepts to a different language, to make working code. The interface designers might need to lay components in the design in a slightly different way. And, the database designer might need to add an aggregation of data for efficiency, or add a join table to make ends meet. However, any unmotivated difference should be considered a technical debt and a target for a refactoring effort.

For the motivated adaptations, the burden of maintaining the respective mapping falls back on each party. If we need to reconceptualise and change the domain model, then each party will need to do both that change and the changes in their adaptations. So, in order to survive each party should keep the adaptations to the minimal motivated level.

Domain models a la Domain Driven Design and data models for database persistence should in a sound system resemble each other. But, they are not the same as they fulfil different purpose, and should not be confused with each other.

Yours

   Dan

PS When trying to keep down the semantic difference between the domain model and the data model, you probably want to refactor the database continuously - something that gets a lot easier if you version your database.
PPS A related question is to what extent technical details are allowed to be part of the model.
PPPS Domain Driven Design is in general a large and interesting field in and of itself.

Version of Data in Database

Dear Junior

I recently had a database versioning going into production. And by database versioning I do not mean that we know what patch of level the database management system (in this case IBM DB2), but the business data itself.

If you never or seldom change the database definition you can easily keep track of changes. However, with development agile style we constantly change our system either by adding functionality or by doing refactoring of the structure, and then we need to change the database as well.

Sooner or later, we run into the situation where we want to test some functionality, get a backup from production, and then wonder: "ok, so what structure changes do I need to do to get this usable?" It might be easy to see what tables or columns are missing and should be added. However, we then rely on a fresh nights sleep and good morning coffee, and still we might miss more subtle changes. And, trust me, this gets even worse if it is not a production copy, but some test data database that have been lying around and where disparate groups have performed random experimental changes "just to see what it would look like" - changes that later may have made it to production, or not. In that case you should expect turmoil of inconsistencies.

The best way I have found to get out of this dilemma is to assign each version of the database structure a number. Of course you need some place to start, and at this last occasion we selected what was in production on 2008-01-01 and announced that as structure zero. The idea is that every change of the database must declare what version it is a change to. So, instead of saying "add column FOO to table BAR", you must say "if the database is in version 32 you can get it to version 33 by adding column FOO to table BAR". So, this would be the SQL-code for "patch 33".

The information on database structure and the patch-scripts are of course in our subversion in a directory of its own: "database" and with subdirectories "baseline", "patch001", "patch002" etc.

The baseline in our case is nothing more than the DDL for recreating the database structure as it looked in production in 2008-01-01, plus scripts for inserting "base data" that has to be there and is not managed by Canatala (which is the name of the application).

In this case the application connected a bank to several insurance providers; the base data it needed was information about the insurance companies. (In fact, I have substituted the application and the domain for non-disclosure reasons, but the problem is the same.) Anyway, this data was not managed by the application, but imported as a batch on first deploy, and then enhanced by inserts at later deployments.

To get really down to earth the directory contains twelve files.

• baseline-sql01-create-structure-ddl.sql
• baseline-sql02-insert-basedata-insurance-providers.sql
• baseline-sql03-insert-basedata-xyz.sql // some other base data
• baseline-sql04-insert-basedata-uvw.sql // yet more base data
• baseline-sql05-sp-PROVIDERSYNC.sql // a stored procedure
• baseline-sql06-sp-XYZ.sql // another stored procedur
• ...
• baseline-sql12-sp-UVW.sql // last stored procedure

Note that the files are named in a way that enables a script to automatically apply them in the right order using the filename up to the second '-'. At the same time the file names describe to a human eye what the SQL is about, without disturbing the script. We were kind of proud over that balance.

The "patch" directories contain all the SQL-files needed to take a database from one schema version to the next. As an example, we shortly thereafter came across a new concept "insurance delegate", which we also needed to store in the database - thus a new table INSURANCE_DELEGATES.

This table also needed to be filled with some data to match the existing insurance providers. All this became patch 002, so the "patch002" directory contains the files needed to take a version 001 database and upgrade it to version 002.

• patch002-sql01-create-provider-delegate-table.sql // creating new
  table
• patch002-sql02-insert-provider-delegates.sql // filling the new table
• ... // another script excluded, you will soon see what it is about

I think you got the grip by now. If you need to upgrade a database, just run all the scripts in all the patch-directories of a "higher" version then what you have. If you need to create a database from scratch, use the baseline and then run all the patch scripts.

So, but how do you know which patch-scripts to run, i e how do you know what patch-level you are at?

The really good part is that this version information should be *part of the data*. In this last case we added a new table defined by:

--- Table on database version (current and history), when patch was
--- deployed, and what it consisted of
CREATE TABLE CANATALA_DB_VERSION(
CANATALA_DB_VERSION BIGINT,
PATCHDEPLOYTIME TIMESTAMP,
DESCRIPTION VARCHAR(256)
);

And then, whenever you apply a patch, you change the data in this table to reflect the change of patch level.

Actually, there are two ways of using a table like this. Either you just save the information of the current version, or you save the entire history. I have done both, and settled for saving the history because it really helps when you "find" some database lying around and wonder about its life hitherto. Next time I will also probably extend it with some kind of environment information, e g an extra column storing the IP-address of where it was running when it was patched to distinguish if it was patched in production, or upgraded after being copied from production.

As you might have guessed, this table was not in the database when we started, so the CREATE is not part of the baseline-sql01-create script. We had to introduce it.

Actually, introducing the version table was our first patch, patch000. So directory patch000 contained two files, one introducing the structure, and the other inserting the data that "this database is now of patch level 0".

• patch000-sql00-create-version-table.sql
• patch000-sql01-insert-dbversioninfo.sql

First file contains simply the CREATE TABLE I mentioned earlier. The second file contains the INSERT needed to insert "version 0" into the table:

INSERT INTO CANATALA_DB_VERSION
(CANATALA_DB_VERSION,PATCHDEPLOYTIME,DESCRIPTION)
VALUES
(0,CURRENT TIMESTAMP,'Starting versioning of database');

I guess this makes it pretty obvious what is in the file I left out in patch002 - it is the db-version insert for that patch.

> patch002-sql03-insert-dbversioninfo.sql

INSERT INTO CANATALA_DB_VERSION
(CANATALA_DB_VERSION,PATCHDEPLOYTIME,DESCRIPTION)
VALUES
(2,CURRENT TIMESTAMP,'Adding support for insurance delegates');

If we follow this scheme, we can take a look in any database and see its patch-history and decide what patches we need to bring it to a usable patch-level.

Of course, this model is not perfect - there are several ways to improve it. For a start, there is no guarantee that each patch actually will contain a dbversion insert. One way to fix that could be to have the patches be applied by some kind of special tool. That tool could check that the patch directory actually contains such a file and otherwise refuse to apply the patch. Another drawback is that it is possible to apply a patch to a database of the wrong level, e g applying patch 34 to a database of version 31. You might even accidentally apply the same patch twice. This is also something a tool could check.

Even if there are plenty of ways to improve this model it is a really good way to start.

But the best part has not even started. Once you have got versioning of the database under control, you sudden have got the possibility to start working with the database in a new way - such as doing refactorings of the database structure. And we can get agile in the database tier as
well.

Once you have gotten your toes wet, there are lots of places to dig deeper. I would like to point out two. K Scott Allen has written five blog posts on database versioning that are really well worth reading [see Jeff Atwood's blog at http://www.codinghorror.com/blog/archives/001050.html]. However, thereafter I really recommend "Refactoring Databases" by Scott Ambler and Pramod Sadalag where they really show the power of the approach.

Yours

Dan

ps A great thing with having a versioned database is that you can start refactoring it to mitigate the mapping between the domain model and database design.