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