In addition, ERwin has a free community edition of their data modeling solution.
ERwin Community Edition
The community edition supports a model of up to 25 entities.
Some of the commerical products also offer a trial edition. Toad offers a 30 day trial of their data modeling solution:
Toad Data Modeler
All of these tools support forward engineering (generating code to create a physical database) and reverse engineering (importing an existing database to create a model).
Open ModelSphere was the first program I tried. The designer seems less intuitive than the others, but the site provided an extensive user guide. The program also has a wizard to guide a user through the process of setting up the model project.
It supported three model types: Data Model (Relational), Business Process Model (Process) and UML Model (which includes options for class diagrams and use case diagram, among others).
For the Data Model option, you can create a Conceptual, Logical or Physical model.
The parent company does offer paid support and professional services.
SQL Power Architect is open source as well, and offers a free community edition. They also offer an Enterprise edition, with multi-user support, extended security features, etc.). They also offer paid support and professional services.
There is very little available in terms of help. There is a user guide available for a fee.
However, the designer was easier to use than Open ModelSphere’s, it seemed more intuitive to me. There was also a feature to compare a data model to a database and generate delta scripts.
As you would expect, the paid tools (ERwin and Toad were the ones I looked at) offered more features and were more user friendly, but you’re either limited by the number of allowed entities (ERwin) or the price (for Toad).
I’m going to use SQL Power Architect going forward, it’s hard to beat no cost, plus a good range of basic features. However, this means I’m left on my own to figure out how to use the programs (Unless I break down and buy the user’s guide, priced at $95)