Lookup Tables: Are they a leak in the domain model?

by Drew   Last Updated September 27, 2018 18:05 PM

You are building a system that keeps track of Companies. Those companies have Contacts. Those contacts are often specialists that only answer certain types of questions, such as Billing/Payment, Sales, Ordering, and Customer Support.

Using Domain Driven Design and an Onion Architecture, I've modeled this with the following types:

  • Company
    • Has Contacts
  • Contact
    • Has Contact Types
  • ContactType (enum)
  • CompanyRepository (interface)
  • EFCompanyRepository (defined in an external assembly, uses EntityFramework, implements CompanyRepository)

Our team has a split opinion on how to model the database for this application.

Side A: The Lean DDDers:

  • It's the job of the Domain to define which ContactTypes are valid for a Contact. Adding a table to the database to validate that unknown ContactTypes are not saved is a sign of a leaky domain. It spreads logic too far out.
  • Adding a static table to the database and corresponding code is wasteful. In this application the database solves one problem: persist the thing and give it back to me. Writing an extra table and corresponding CRUD code is wasteful.
  • Changing the strategy for persistence should be as easy as possible. It is more likely to change that business rules. If I decide that SQL Server costs too much I don't want to have to rebuild all the validation I put in my schema.

Side B: The Traditionalists [that's probably not a fair name. The DBCentrists?]:

  • It's a bad idea to have data in the database that doesn't make sense without reading code. Reports and other consumers have to repeat the list of values themselves.
  • It's not that much code to load your db type dictionaries on demand. Don't worry about it.
  • If the source of this is code and not data I will have to deploy bits instead of a simple SQL script when it changes.

Neither side is right or wrong, but one of them is probably more efficient in the long run, counting development time for initial development, bugs, etc. Which side is it - or is there a better compromise? What do other teams writing this style of code do?

Answers 4

I have found it best to store enums as their string representation in the database and not include a lookup table.

Obviously the downside to this is that it uses more disk space and is not normalised.

The plus side is that the field retains its meaning in the db, you dont end up with magic numbers corrosponding to the int value of the enum for example and you dont have to manage versioning a lookup table when the enum changes.

Not sure I would characterise this as a DDD vs Trad difference though. Its more a database-centralist vs code is best in my view

October 27, 2015 13:28 PM

Domain objects stop being domain objects when they cross a process boundary. Even if the database is just a persistence store, at some point in the future the demands of the business are going to cause a change to the domaim model that is inconsistent with the persisted history, and you are going to need an anti corruption layer anyway....

That said, I think your DBCentrists are missing the boat. The domain modelers are saying that they need a persistence store. "Reports and other consumers" need something that they can query -- something with decent indexes, as has been noted in comments.

Who introduced the constraint that all of these different concerns needed to be supported by one database? What happens if you push back on that.

Search keyword: CQRS.

December 11, 2015 06:20 AM

  • As long as you are using a relational database you should keep tables normalized to a reasonable extent. Normalization helps prevent insert and update anomalies.
  • The one-app<->one-database relationship is no longer common, multiple apps can use multiple databases and a single database can be used by multiple apps, so having the database enforce referential integrity as much as possible is good.
  • The RDBMS is your friend.
  • You can go with a key-value storage instead and do everything in code.
Tulains Córdova
Tulains Córdova
December 11, 2015 13:05 PM

By adopting DDD and onion architecture, you have decided that database is second to your domain model. That means, there won't be anyone else doing operations on the database other than the model. If traditionalists don't like that, they should have objected to use of DDD in the first place.

First thing is clear : you need the "lookup table" in the model. Your model needs to differentiate between different types of contacts. That means, it keeps strongly-typed list of all types. There also needs to be mapping from those strong types to values that are serialized to database. This mapping can be inside the database module. But the list of all possible types will still be in the model. And if the model is to be single source of truth, then it cannot be inside the database. Or at least, when the list changes in the model, it needs to change in the database. And no buts!

December 11, 2015 14:44 PM

Related Questions

Domain Model examples and best practices

Updated February 16, 2019 03:05 AM

Domain Driven Design in an Onion Architecture

Updated October 19, 2017 14:05 PM