Database Design Software

[steeladept]

Okay, I have a very complex database project I would like to start, and I found I can't exactly just create the tables and run with it like other projects I have accomplished.  In my college days, we used PowerBuilder for these types of things; and while I didn't like or understand it's use at the time, I knew it was widely used.  Today I have looked around for a tool like this and found precious few choices that are reasonably priced (<$500 US) for individual use.  What to the designers here use? 

Ideally, I would like free w/ connectivity to SQL Server 2008 Express database, but I think that is something of a pipe dream.  I have found a few FOSS designers, but considering my limited knowledge on these types of software, I need to be certain that it is well documented - a serious issue with ALL FOSS software I have seen.  Also, all FOSS software I have seen limit their connections to MySQL or Postgrl, whereas I would like to see connections to SQLServer and/or Oracle (without too many hoops).  I would think that a simple ODBC type connection should allow these connections, but they do not seem supported from what I have read (note this does not imply it is impossible).

Short of the ideal, I have access to SQL Server 2008 (std & express editions), Oracle (10g personal), Access, OoBase, and any FOSS databases that I can download and install.  All I am looking for is cheap or free design software to connect to any one of these databases that will allow me to visually design and change the database without having to rebuild it.  I have attempted using the OoBase for this project twice already, and both times it has blown up and forced me to rebuild it from scratch.  I am not willing to do that anymore.

Any suggestions?  Answers?  Experiences that would help?

[bmm]

I think I have tried most of the freeware and FOSS database designers and for now I have settled with Power*Architect.

• Java, so works on Windows, Mac and Linux
• Can connect to pretty much any RDBMS through JDBC drivers
• Has built-in data profiling features
• Integration with the best FOSS ETL Tool: Pentaho Data Integration / Kettle
• In active development and questions are quickly answered in the forum

It is not as complete as commercial database designers and is (currently) missing:

• creation of views
• cascade on update/delete rules for foreign keys
• comments on tables and columns are not included in DDL

[steeladept]

Thanks for the info.  I kept looking and found one that looks promising, though I am pretty well stuck with MySQL if I go this route.  It is called DB Designer 4 from fabForce.net.  I just started playing with it and it looks very professional, yet very easy to use thusfar.  It also supports some of the drawbacks you mentioned such as cascade on update/delete rules for foreign keys.  The rest I am not sure about though, and I haven't tried the actual database creation yet, since I am still trying to define a table or two.  Some tools seem simplistic, but most are readily available.  For example, I can't copy a field and make it field2 with the same properties.  On the plus side, with support for enumerated types and sets, I may not need that.  Well that and I can always type it manually 

I will check out Power*Architect soon as the web/interface programming will either be in Java or C# (depending on the system I use, as these are the two languages I know best).  My one question though, for any of these tools, since they tend to be designed for integration with specific databases (via support for the data-types), what happens when you use an ODBC/JDBC driver to connect to a different DB?  For example, MySQL supports the aforementioned SET and ENUM data-types, but OoBASE does not.  If I were to try to use the driver to make that connection, does the driver translate the data-type to a primative that the RDBMS can understand, or does it just blow up?

[bmm]

Now that you mention DB Designer (which is pretty nice) you should also look at DB Designer Fork which is (you guessed it) a fork of DB Designer that can generate SQL scripts for Oracle, SQL Server, MySQL and FireBird. In general this version is still very MySQL focused.

DB Designer is not developed anymore, but is succeeded by MySQL Workbench which keeps it focus on MySQL, but now there is both a FOSS and Commercial version.

I still prefer a more database agnostic software - therefore my preference for Power*Architect.

My experience in general regarding JDBC-drivers is that the client program must serve the correct data-type to the JDBC driver that matches the RDBMS data-types otherwise errors are likely to happen.

[Veign]

Here's a pretty complete list of tools for MySQL (might work for SQL Server):
http://www.veign.com...agers-and-tools.html

For SQL Server check out EMS (their free tool is still powerful):
http://www.sqlmanager.net/

[steeladept]

Thanks to both of you!  I am checking out the Fork now, as that will directly import my XML file I already have started.  Not that it is a lot, but it is supposed to support SQL Server as well, and if it does then this is probably my best ticket.  What I saw from EMS looks VERY GOOD as well.  Power*Architect is still something I plan to look at as well, but my first workings with DBDesigner left me really liking that tool - as long as it can do what I want it to.

Thanks Veign for your MySQL list.  I looked at that many moons ago but forgot about it until now.  That was very enlightening.  Your list mentioned the DBDesigner Fork as well.  Do you have any reason to suggest EMS over the others?  Just curious.

[Veign]

I like EMS for SQL Server and Navicat for MySQL.  Those are what I have found to work best for me.  99% of the time I work in MySQL.