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?
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:
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.