Slashdot Mirror
Recommended Data Modeling Tools?
dnxthx asks: "After performing a fairly comprehensive web search (including Slashdot) I came to the (possibly incorrect) conclusion that there were no high-visibility sites that comprehensively reviewed and compared data modeling tools such as ER/Studio, ERWin, DeZign for Databases, System Architect, or Visio for Enterprise Architects. Since some of these tools can be quite expensive (ERWin is about $4K US it seems), I was wondering what the Slashdot community's experiences were with data modeling tools such as these, or some that our group has overlooked."
http://www.thekompany.com/products/dataarchitect/
$60 USD for the download version. I used it for my last Database design project (first on MySQL, then moving that model to Postgres), and it was great. Made my life a lot easier. Available on Linux, Windows, and OS X.
Modeling on a whiteboard is all well and good -- we did it at my job (and still do it) for all of our initial design.
The problem is, that whiteboard eventually needs to be erased. And when new people come on board drawing the same damn picture on the board over and over is tedious, not to mention error prone and bloody difficult for anything but high level representations.
We copied the whiteboard drawings to paper and then did some basic modeling in Dia. While OSS and Free, it's really not very good for this kind of thing and can't auto-generate mappings or anything like that.
I don't really have an answer here, but a data modeling tool is pretty essential unless you expect all of your developers, QA, management, and support to be around forever. You can get away without it, but you'll burn $4K in man hours pretty fast doing it by hand. I know we've burned considerably more than that and our diagrams still suck and are rapidly outdated. (We're in the process of buying tools... but I'm not in the loop by my own choice, except for the C++ debugger).
I assume that you are working with relational databases, although you don't mention it.. (data modeling could mean a lot of things)
:)
Some DBMSs have some decent, albeit limited graphical design tools (MSSQL, Access..)
together from borland does entity relationship diagramming, and so much more. (great tool)
I believe IBM's rational rose may also do it.
Since my job includes SQL design as a subset of my duties (i'm a developer/architect, not a DBA) these two tools are significantly more valuable than ERWin.
I just wish I could get my boss to buy them.
How it's done is, roughly, for a few related tables:
- Create tables using dia's UML's classes.
- Join the tables using UML Association to establish foreign keys.
- Set dia to autofit diagram into a number of pages.
- Add comments, draw pictures, whatever dia can draw.
- Save as an uncompressed XML file, chuck it into CVS. CVS likes text, so don't compress it.
- Optional: Print out pretty diagrams to printer. Dia's autofit is nice.
Repeat above if you can't fit your tables into a set of nice looking pages.Congrats! You have have fully printable, documentated, usable SQLs, and have version control on the schemas too. (Missing step is "4. Debug")
P.S. tedia2sql is written in easily hackable perl.
Been using Visio for quite some time now.
Negatives:
-Fairly resource-intensive program
-Takes time to find all the features and figure out all the tricks and shortcuts to operating it
-An abundance of features
Positives:
-Allows for creativity in customizing tools/settings/functions for personal use
-Covers many business modelling themes/motifs
-An abundance of features
One key point here is that most dbs that I've dealt with (Informix, MySQL, SQL Server & Oracle) do not let you specify the default value for a new column automatically. That means you're gonna have to script the modifications to insert/compute the new column anyway.
:D
Dont really know about the rest, but in Oracle you just do a
create table foo (bar varchar2(10) default 'whatever')
Not quite sure, but this sounds like ANSI92 to me.
Of course, maintaint anything else than a 5 tables model by hand is a big PITA.
And also, as a mini-rant, I have to say that the use of foreign keys has *never* been necessary in any db I've designed (and I have created very complex db apps with 30-70 well-connected tables).
Well, that's just plain wrong. Declarative referential integrity saves a LOAD of work, makes your data model self-documenting, easier to maintain, and believe me, most modern databases know have query optimizers that work wonders on them.
Of course, unless you charge by the hour, want to have to code on each of your 50 tables for such a basic thing as referential integrity, and want to make your database a pain to work with. If that's your objetive, you can also code primary key logic too
My point here is that your accessor logic (so-called middleware) is what manifests the "foreign" relationship and if that's screwed then your app just doesn't work. Slowing the db down to do all that referential integrity is a waste of time. You've either got the middleware right or not.
It really doesn't slow things much, as long as you have the proper indexes, which you should have in any case, as usually foreign keys in a data model represent relationships you are going to use to join different tables...
This is, unless you are working with a very toy-like data model with 100 records on each table.
Perhaps it might have been better to list what feature's you're looking for in a data modeler, that way we can point you in the right direction. Still, I'll blather on about my fave...
;)).
I use DBDesigner4. It's free/GPL, so the price is at least a feature you're looking for. It's also available on Windows, & KDE/Gnome (not sure what widget toolkit it uses), so that's a plus (or minus depending on your religion
It's also fully optimized for MySQL if that's your platform, but it can support any ODBC database, Oracle, or MSSQL. It has a very intuitive (and pretty, IMO) graphical interface, with great, easy-to-use tools for visualizing, grouping, and relating your data. It also features a graphical Query Builder that lets you point/click your way through complex queries, returning SQL you can insert into your code. It allows reverse engineering of any database it can connect to, as well as synchronization so you don't have to do the setup work after you model, like you might with, say, Visio.
It's not UML; it's really designed specifically *for* relational databases, so if you're just looking for a UML data diagram builder for internal data representation, this is probably not the tool for you.
But if you are using a relational database, I don't think you can go wrong with it. It's at least worth a download.
"Times have not become more violent. They have just become more televised."
-Marilyn Manson
And personally I find them to be about as useful as a pen and paper, and much more expensive. Of course, I felt the same way about our DBA (she was about as useful as a pen and paper, and much more expensive).
Just in case this isn't a deliberate troll to provoke database weenies:
/sarcasm.
If you don't have referential integrity enforced via foreign keys, how do you know your "accessor logic" is correct? Or more importantly, how do you know when it's wrong? Trust me, if you do not enforce referential integrity you can have subtle problems that do not manifest until after you have corrupted your data, long before you're at the "just doesn't work" stage. (Eg, child records with no parents). It is far, far more sensible to leave this to the DBMS to manage and optimise the rare cases where this might cause performance problems. I'm sure you're a god who gets this right in every single case without the DBMS' help, but we mortals are better off leaving it to Oracle (or Postgres, or whatever.)
And yeah, if you don't know what nulls are for, don't use them.
The results for Best Modeling Tool (scroll to last chart) are:
- Winner: Visio 2003 - 37%
- 1st Runner up: Rational Rose - 18%
- 2nd Runner up: Oracle9i/10g JDeveloper - 7%
Eight other products are on the chart.If you are SERIOUS about your data modeling, ERWin is probably the best way to go. Many professional DBAs probably couldn't do their jobs without it. I would also consider Rational Rose to be on a par with ERWin, but it's ownership by IBM has rendered it "partisian" in some DBAs minds, as is Oracle's product. Erwin has the advantage of not being owned by any company that sells a database, generates code for all of them equally, and is mostly transparent across all of them, especially when transitioning between logical and physical designs. This especially makes it a favorite of consulting companies that have to move from client to client...ERWin also has an excellent tutorial on HOW to do data modeling, which can at least serve as an entry point for someone new to it's practice
On a side note, ERWin is NOT as object model-centric as Rose and some others, as it is old enough to have been developed before object modeling became cool. But that is a minor quibble.
I also find a good set of 3x5 filecards (taped up to a whiteboard or large construction paper) an excellent starting point for my models, particularly when trying to model those main logical entities that end up driving the entire design. They have the advantage over whiteboards of being at least partially on paper should someone erase the board...
ONE VERY IMPORTANT ISSUE: How well does X software import DDL from Y database and generate DDL for Z database. This is important. This shouldn't be an issue but sometimes it can be. If you have to import data from an Excel spreadsheet, existing database (like mSQL, MySQL), or some exotic ODBC datasource, can X software handle it. Frankly, that's something a professional magazine has to test. That's were downloading said software in trial mode (should be fully-functional) becomes really important. Test this out for yourself. Don't believe the brochure. I had a couple of issues with this. You may be able to import via ODBC, but will you have to edit/grep/replace DDL before doing so. Trivial but annoying...
ERWin also allows the user to seperate the physical model from the data model by design. I don't think DeZign allows you to do this. This may not be an issue for you.
To be honest, although I really like ERWin, I can't justify it's expense unless you work for a bank. Take that however you want it. Seriously, it's expensive and definitely overkill for the simple developer/architect. However, if you're a DBA it's very useful.
I used to use ERWin, but just recently (at a new job) I'm using DeZign. It gets the job done and it's cheap.
What do you mean my sig is repetitive? What do you mean my sig is repetitive? What do you mean....
This means that referential integrity in the database serves more than to make up for bad middleware, it means that this is where business rules and other items that rarely change can be maintained. [...] Another thing that people often overlook is the fact that many times a database serves more than one application.
I don't overlook that; I actively repudiate that.
One of the fundamental notions of object-oriented programming is that is an object is data plus behavior. If you have several different chunks of code manipulating the same raw data, then that's not object-oriented programming. In which case, fine, throw out that Java and Python and go back to COBOL.
Theoretical reasons aside, there's a very practical reason for making sure that exactly one chunk of code reads and writes your raw persistent data: schema migration. As needs change, so will the schema of your persistent data. Keeping one code base in sync with that is, with much discipline, possible. But I've never seen any place that can keep multiple code bases in sync with new schemas.
If you can't migrate your schema, then you have massive design interia, meaning developers spend more and more time focusing on just making things work, rather than making new features. Which leads to dissatisfied executives, which leads all sorts of sadness.
Having your middleware control what items are stored in a table is just looking for trouble IMHO.
Without the right approach to development, this is true. With it, whether or not you're using a database at all becomes a peripheral question, rather than a central one.