I'm starting a new database application and I wonder if it would be better to start the design at the objects (with UML) and build the database schema accordingly, or start at the design of the database (with ER) and create the objects accordingly.
What are the pros and cons of either approach?
(I dont think it matters but just in case: I'm planning to use Java and Hibernate)
Starting at the database level is definitely going to allow you to create a database that is more easily handled by a regular relational database. So, if you expect to get hundreds of thousands or even millions of objects, this is the approach I would take. However, if you do not expect to get that much data, or if performance is irrelevant, I would go with the object approach since it will allow you to model a solution that will fit more fluently in with your actual application.
The pros of starting with the database is that you will have performance and your DBAs in mind.
The pros of starting with the OOP side is that you will have a purer OO design, but your ORM could be underperforming.
I find black-box ORMs too limiting, so my systems are all coded by hand or code generated and modified, so my databases are usually pretty tight AND my OO model is pure. I strongly believe that smart humans are the solution to the impedance mismatch.
It depends on where you want the best performance. If you expect the database to be your bottleneck, then start designing there so you can tune it for performance. If you're only using a database to save the state of a few objects that need to be performant in your application, then design them accordingly. O/R mapping software isn't as reliable (yet, and they aren't going to catch up soon) at performance tuning as a good compiler is. You'll still do a better job of designing for performance (albeit at a higher upfront development cost).
Depends on whether your application is designed to meet user needs, or to meet the needs of developers.
Start with the user stories, and get the OOP Classes straight. And your users will love you.
Otherwise you end up with an application that turns users into data entry clerks. (You'll have lots of company.)
If you are writing an OO Application then I find that designing the objects first is the place to start. I'd rather create a good OO Design and then attempt to optimize the database rather than build a database then try to fit objects to the database.
ORM tools gives you the freedom to focus your attention on the object design.
However, it does not replace good Database Design. You still have to keep an eye on the database. I find that with careful thought, the OR mappings can be done so that the database created is fairly optimized. You can always look at the queries and attempt to optimize them if you find some queries are underperforming.
You should not have to build an application that conforms to a database model, the database should be designed to support your applications. And ORM tools do allow you to create optimized databases and queries.
That depends. Are you doing object-oriented programming or SQL-oriented programming?
Either approach can work, and each has its pros and cons. The DB-centered approach is great for many apps that share data. The object-centered approach is great for getting a single app up and running.
If the DB does not exist, I'd consider building the app without worrying about it. Maybe something like db4objs would give you the object persistence you need while you evolve your app into something useful. Later, when new apps are designed to share the data, or ad hoc query becomes important, a relational DB mapping makes more sense. Maybe you will never need relational. Build it when you need it.
If the DB already exists, or it is a requirement to get your data there, I'd still try to defer the relational work as long as possible and get the objects and the code working. But I'd change my mind if I saw myself writing complex code that could be done by SQL.
It depends on where your strengths are, and where you want to go.
You can start with objects, and do a pretty good job, including good table design in your database, if you start in the right place.
Start with object oriented analysis, rather than object oriented design. I can't emphasize the difference between analysis and design strongly enough. People who write about about object oriented analysis, some of whom use UML as a tool, try to make this distinction clearly. Analysis pertains to the problem domain, while design pertains to the solution domain. These can easily be mixed up with each other, whether your approach is object oriented or not.
If you do a good OOA analysis of the requirements of your project, you can probably do a reasonable job of constructing a conceptual data model in parallel, and keeping those two in synch with each other. When you build a conceptual data model, I suggest you keep to a model like the ER model.
An ER model won't tell you how to design your database. That's the whole point. The way you separate analysis concerns from design concerns in data modeling is to do your analysis using ER modeling and your design using relational data modeling, at least at the beginning.
The mapping between OOA and ER is simple enough so that you can manage the two models in parallel. There may be newer tools that manage both kinds of models for you. The mapping between ER and RDM is deceptively simple. In the simplest mapping, you turn each entity into a table keyed on the entity's identity, and each relationship into a separate table keyed on foreign keys that refer to the entities. It's possible and desirable to reduce the number of tables by plugging some foreign keys into entity tables, but that's a detail.
From OOA, you proceed to OOD for design and OOP for programming.
From ER for conceptual data modeling, you proceed to RDM for logical data modeling and to your DBMS specific dialect of SQL for physical data modeling. There are definitely tools to help you with this, if your project is too large for modeling on paper or whiteboards.
Periodically, you build what you've got, maybe with stubs for the unbuilt part, and you reconcile your application design with your database design. If you do a good job, at the end of the day, you should be in pretty good shape.
If you are designing one database and several applications at once, things get really interesting.