ORM modeling: Database first vs classes first

Question

I'm attempting to use Sparx Enterprise Architect to design a data model that will end up in a MySQL database.

My first approach was a Data Model diagram, which can be used to generate DDL (or the other way round by reverse engineering).

This works quite well but a colleague pointed out a snag: We're intending to use an ORM (almost certainly Hibernate) to map tables to Java classes. His comment was a "database first" approach would preclude the use of good OO techniques such as inheritance.

This seems a good point but I'm wondering if there are any limitations. If I started from scratch with a Class Diagram instead of a Data Model diagram, would there be a way of including all necessary Hibernate annotations, config etc. in this model? And if I later needed to model database-specific functionality such as constraints, triggers etc. would all this be possible in the model given that a Class Diagram isn't really aimed at this type of thing?

Rosario

I always prefer start with a Data Model diagram and from this, create my Class Diagram and then map my classes in a way that simplifies my work as much as possible. – Detachment

Thanks, do you mean you produce both manually? Would ideally prefer to only have to maintain one diagram and use it as a single source for generating both classes and database schema (with a minimal amount of "wiring" outside the model). – Rosario

In that case, I would maintain the Data Model diagram, but I think the answer depends on the person itself and on the way you prefer to work. – Detachment

Well I'm more familiar with database-first approach, having never done the classes-first approach. The question is really about identifying anything that couldn't be captured in the model (i.e. Class Diagram) if doing it this way. – Rosario

Answer 1

Regardless of the technology you're using, you should always go "truth first". Where is the truth in an XML interface? In its XSD specification, not some implementation classes in any arbitrary language. Likewise, where's the truth when interacting with an RDBMS? It's in the database, written in the form of DDL. The database should "own" its schema, not have it generated from some derived client representation. I've written about this topic here.

This is the only reasonable way to stay in control of your database schema in the language that matters to the database. This is also the only way to reasonably:

• Evolve the schema once you go live and cannot just simply drop and re-create it again
• Keep in control of database performace, especially when you write SQL queries rather than using the ORM to navigate your entities.

We're intending to use an ORM (almost certainly Hibernate) to map tables to Java classes. His comment was a "database first" approach would preclude the use of good OO techniques such as inheritance.

You should ask yourself why you need inheritance in the first place. Since you're storing your data using the relational model, you should use the relational model's modelling features, and all client representations (e.g. your ORM's) should be derived from that. In very rare cases, inheritance is even a viable modelling technique in this area, and mostly it still doesn't work well, because after more than 20 years of OO, people have concluded that inheritance was overused in early days of OO - especially inheritance of data structures. Composition should be favoured, and is more relational anyway.

Chances are that your relational model will outlive your OO client representation and you should make sure that the relational model is sound and normalised, etc.

This seems a good point but I'm wondering if there are any limitations. If I started from scratch with a Class Diagram instead of a Data Model diagram, would there be a way of including all necessary Hibernate annotations, config etc. in this model? And if I later needed to model database-specific functionality such as constraints, triggers etc. would all this be possible in the model given that a Class Diagram isn't really aimed at this type of thing?

I don't think you should need to navigate your database model through a derived class diagram. Think in terms of your ERD (which you can generate from your DDL). The ORM's representation will simply reflect that.

Hatch

Answer 2

I prefer to model the database first. The database is the most valuable part of the business, the application logic being only an interface to manipulating the business data.

Since databases tend to outlive application technologies, it's better to design it upfront since the design is usually driven by data relationships and the data querying model.

Most ORMs were designed to model domain objects to existing schemas and it's the ORM tool task to cope with any possible DB schema mapping concerns.

For rapid prototyping, I might consider having my schema generating from the domain objects but when designing a large enterprise system architecture this approach is sub-optimal.

Hibernate only offers a limited number of DDL features and I don't like to loose extra DB specific features like PosgreSQL domains, instead-of triggers, materialized views or MySQL triggers.

A tool like Flyway is best to use to automate the schema migration process.

Dumps

Some good examples, however none of these seem relevant to MySQL. Some of the things I'm wondering about are MySQL triggers and all the Hibernate annotations controlling indexes, foreign keys and referential integrity (ON DELETE RESTRICT/CASCADE/SET NULL/NO ACTION) etc. – Rosario

Answer 3

Let me answer by asking a question: if you want to construct a house, will you build it first and then make a blue print or you will firstly make plans? :)

Software development is all about lowering gradually the abstraction. We start with a very abstract project idea, then make some reqs (which is clearly a bit less abstract), then the architecture, design and finelly be come to the coding level (the lowest abstraction)

Data model is the model on the lowest possible abstraction level (as directly mappable to DDL), so this is the last thing you will do.

Domain class model is a higher abstraction of the database. It's even abstraction of the Hibernate layer, as it also lays on the implementation level of abstraction.

So, I would first definitelly model a domain using classes and full power of OO. Try to make implementation independent class model. Do not assume JAVA, Hibernate, DB, anything and concentrate on your domain logic instead. Make a kind of "utopic" domain model, logically perfectly structured domain classes.

Then derive both Hibernate layer and DB itself from this model, using corresponding conversions.

Chader

Answer 4

Domain Model first, always and ever. Then DB and Entities at the same time.

Micaelamicah

Answer 5

here is one vote for domain driven design, classes/code first.

Create the hibernate entity classes first, thats what you will be coding to - get these right, why care what is persisted to - as you don't plan to write sql (?). My process is create the classes, spit out the uml from them if you need it (very rarely have the luxury of that resource though) and let hibernate build the db.

And I try and avoid vendor specific database functionality - by definition it will be slightly weird.

Clarineclarinet