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

Comments

Data Architect by The Kompany

[hotgazpacho]

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.

Re:Data Architect by The Kompany

[Anonymous Coward]

The download version is now $39.95 The physically shipped version is $49.95.

Re:Some make pretty pictures...

[Zathrus]

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

Some options..

[maunleon]

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. :)

dia & tedia2sql

[szap]

We have been using dia and tedia2sql for many of our projects. This includes a project involving about a hundred tables, with foriegn keys all over, and a number of user defined functions (aka Stored Procedures) and aggregate functions.

How it's done is, roughly, for a few related tables:

1. Create tables using dia's UML's classes.
2. Join the tables using UML Association to establish foreign keys.
3. Set dia to autofit diagram into a number of pages.
4. Add comments, draw pictures, whatever dia can draw.
5. Save as an uncompressed XML file, chuck it into CVS. CVS likes text, so don't compress it.
6. 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.

1. Create a Makefile to convert .dia to .sql using tedia2sql, and .dia to .eps using dia. (Left as an excerise to the reader). Bonus: autogen .eps to .pdf.
2. Set up tedia2sql config to generate to your favourite rdbms. Or change Makefile to generate SQLs for each of Oracle, PostgreSQL, MySQL.
3. Type 'make'.

1. 5. Profit!

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.

Visio

[Ty_Webb]

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

random rants

[bmac]

First, Visio is *out*. Basically, it just plain sucks. All I've found it useful for is *very* rudimentary diagrams. It's so slow and cumbersome that I would never chance depending on it for a live db schema. I really wouldn't want to use it for even creating parallel docs for an existing app, it's so bad. (BTW, I have .NET for Enterprise Architects, which includes the newest Visio. Sidebar: VS.NET is great for prototyping but I wouldn't trust my reputation on an app created with it -- it's my bootstrapper.)

Second, after years upon years of experience, I've come to the conclusion that the most effective technique to use is to have a script that creates and populates the db. Tie this in with functions that do backup/restore to/from text files and you can recreate the db (schema *and* data) from any save point from pure init forward. The other advantage to this is that I despise the 'alter table' command; I find it much easier to just drop the table and then re-create and re-populate it. 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. And, forget about modifying a column type, for extremely non-trivial changes it is nearly impossible (the exception being the changing of a char's or varchar's length).

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

And unless you're doing hardcore statistical analysis, *never* allow nulls. If you allow nulls in *any* field, your query logic (read: the app logic itself) must be an order of mag more complex to deal with results that have nulls in their field values. Blech! Instead, disallow nulls and be careful to initialize your records properly.

Good luck.

Peace & Blessings,
bmac

For true peace & happiness: www.mihr.com

Re:random rants

[1001011010110101]

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.

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 :D

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.

Re:random rants

[judd]

Just in case this isn't a deliberate troll to provoke database weenies:

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. /sarcasm.

Re:random rants

[Anonymous Coward]

And yeah, if you don't know what nulls are for, don't use them.

Heh. What are nulls for? I always found it just adds confusion. For instance, on a string field, why not just use ""? With "" and NULL, now you've got two fields to represent the same thing. I mean, yeah, I guess it's possible that you would want to distinguish between an empty string and no string at all, but in practice I've never seen it.

Re:random rants

[1001011010110101]

For instance, on a string field, why not just use ""?

In fact, in Oracle last time I checked "" IS null
Odd, eh?

Re:random rants

[bmac]

I've found that subtle problems are the result of not properly designing, then incrementally building with iterim testing. And, it certainly does not take a "god" to make bug-free software, but I don't tolerate bugs in my software, because that causes my employers to have to call me, and I'd rather let them have their business logic as they want it and leave me to enjoy life.

More specifically, do you not design your software such that all your business logic for a particular table is located in the same place? How can you possibly strand a child as a result of calling a delete within that lib that calls the appropriate deletes in the child libs? If you are running an app that has stranded children you have progressed too quickly past a point where your implementation hasn't been adequately tested, neh? I mean, you have the purpose of the db in design form on some level (use-cases, formal specs, whatever...) and you implement that piece, test it with all types of parameter sets and, voila, you *know* it works. Plus, you've got the ability to generate log files for any path of logic you choose during Beta so any problems have a trace, and a proper debug trace gives you the ability to track and squash the bug, right?

On a more simple level, assuming you're a programmer, if I asked you to write a program that let the user enter strings into a list that was kept in sorted order, you could generate a program that you *knew* was correct, right? Well, with more complexity in the logic (software logic has exponential expansion, as I see it) it just takes more experience and delicacy in the development phase, where the order of implementation, care in refactoring and beligerant and relentless testing are the difference between success and being fscked.

And the DBMS can *not* manage anything because the DBMS does not spec out and implement business logic. Besides, in any sufficiently complex app, there are constraints on the values in linked tables; these type of constraints are not defineable in any db I've ever seen. As well, one can often have one field that will link to many different possible tables, depending upon values in the parent record. Perhaps that kind of constraint can be quantified in a structural definition, but, to my way of understanding it, it is more of a temporal (or dynamic) condition. Even if it could be spec'd into the schema it's no better than an assert statement (which I loathe), and certainly does not help you generate the functionality necessary to successfully construct the data.

Unfortunately, it seems that my sharing my experience is wasted on this rather abrasive crowd. I spent six months developing a monster of an ugly intranet db app for a Finnish-based international company. Up until the last two weeks we developed it using Linux and MySQL and in the last two weeks transferred it to the runtime env on Windows NT and Oracle. That was two weeks before I had to fly home from Finland, where they put me up after flying me there after the first three months. As a subcontractor, I was liable for all bugs within the project at my own expense for a year after delivery. After two months of testing, they found six bugs, all but one *very* minor. I spent eight or ten hours fixing them and then *I was done*. And this was no trifling app; it was the most aweful convoluted business logic *ever*, built with raw html and perl (not my choice).

I'm really not trying to toot my own horn, I'm just trying to let y'all know that I know my sh*t, and if the lack of a foreign key causes problems in your app, the DBMS is not going to fix your problem. The problem goes much deeper.

Peace & Blessings,
bmac

Re:random rants

[bmac]

The default value thing I was mentioning had to do with altering a table that has data already in it. Giving the new fields in the existing records a default value has not been possible in the envs that I've worked in. This is related to another problem with adding a column after table creation, namely, that the column must be able to hold nulls, which screws up query logic, as I've found over and over.

And, as you say, most foreign key relationships are for indexed fields or groups of fields, which to my understanding would mean that that is enough information for the query optimizer to perform the query in the proper order (and let's just say that index design is where you prove where you know how to design a RDBS). I mean, no one with any clue joins on non-indexed fields, right?

And, as a matter of fact, though I design my primary keys and any secondary indexes with great care, I *do* perform the primary key checks before I ever issue an insert. Why?

*silence*

Because the insertion of a record can fail for any number of reasons: a bad key or a too-long char/varchar as two examples. Now, with two unique keys (say a primary int id and a unique name) and N string fields you have N+2 possible ways that the insert can fail. I don't like to parse db error message strings, so I perform N+2 select queries beforehand to ensure that if the insert *would* fail, I know exactly for which reason. Why? So I can tell my n00b users what they screwed up *in detail*.

And, you are most certainly correct! I do charge by the hour, because only God Himself knows how long anything is going to take, especially the successful design, understanding, implementation and testing of any halfway sophisticated piece of software. Any other pricing policy is just plain old horsesh*t. Either that or a friggin shot in the dark. My favorite formula for project estimation is: take the time you think it will take you, multiply it by three and add two.

Peace & Blessings,
bmac

Re:random rants

[1001011010110101]

The default value thing I was mentioning had to do with altering a table that has data already in it.

Again, in Oracle:

alter table foo (add column bar varchar2(50) default 10)

The rule is, the columns you add must be either nullable or have a default. Check the manual, you might be amused on the things you can do.

I *do* perform the primary key checks before I ever issue an insert. Why?
I don't like to parse db error message strings, so I perform N+2 select queries beforehand to ensure that if the insert *would* fail, [...]

You are kidding me, Right? so instead of doing a single insert, with a single table lookup, you turn it into N table lookups for each partial key? That's a really bad practice performance-wise.It multiplies the transaction load (hardware resources) needed. This includes memory, cpu, disk, etc. Parsing the message is much cheaper, especially parsing it on the frontend.

And, you are most certainly correct! I do charge by the hour, because only God Himself knows how long anything is going to take

That must give you a lot of time to read slashdot. You are lucky, as you seem to can get the users to pay for something for which they dont know how long it will take to code...and lucky because your user didn't find out someone with some more proper DB design techniques which might get the job done at a fraction of the price with a more solid data model.

I mean, this stuff you are writing about can work for a small table model, with very little data, but as soon as you starting to scale the model it gets really ugly really soon.

Re:random rants

[Anonymous Coward]

I saw all your comments in this thread and you obvioulsy think too much of yourself. You still have to learn how fully exploit a *real* DBMS.

Ever heard something about triggers ? Or stored procedures ? MySQL is all fine, but prototyping in it and then porting the DB to Oracle is kind of silly IMHO.

Re:random rants

[dubl-u]

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.

Well, there are two ways of looking at the typical system. Take a web application as an example: Either a) a database is where good objects go to rest when you don't need them, or b) your code is basically an engine for transforming HTTP requests into SQL statements, and then transforming SQL result sets into HTML.

I think you and I are in the first group. The database-centric approach is popular, but I think it is pretty limited, and it introduces too much design inertia. These days I just write my objects in code, and then use an O/R mapper like Hibernate to generate my SQL. If, that is, I use a database at all. Those who can't imagine life without databases should contemplate Google's architecture. Or take a look at Prevayler, a radically different way to build apps.

You've either got the middleware right or not.

Well, here I'd disagree. If referential integrity checks in the database are pretty cheap, I don't see anything wrong with a belt-and-suspenders approach. Even with extensive unit testing and pair programming, I can't so far get my bug rate much below one bug per month. If the database can catch some of those bugs sooner, I'm willing to trade a smidgen of performance.

Of course, as I'm writing this, it occurs to me that databases aren't so great for expressing all the constraints on the data anyhow; if I really want a mechanism for double-checking persistent data, I should just put it in the persistence layer. That would allow me to use the same mechanism on all data, persistent or not.

Ok! You've convinced me. Foreign key constraints are the wrong solution to the problem.

Re:random rants

[bmac]

Though I'm not a statistician, I have done for-pay work (may years ago) using SAS and SPSS to help people analyze their data. As a result, I can tell you that NULL is used as a label for "data not present", which is used in certain stat algorithms to determine, as I understand it, a) the probability that your conclusions/results are meaningful, and b) that you have enough data to draw conclusions.

If, for example, your varchar(n) field type didn't have the ability to store a null, you would have to specify (in your logic) a value for "data not present", which for some apps would not equal "". Add to that the perfect probability that some person would enter the exact string into the field that you are using for "data not here" and you can see the problem that having nulls can solve.

I've never dealt with an app that so badly needed to know that there is no data in a particular field where just initializing it to "" or 0 wouldn't work. And my point here about nulls is that for probably 99% of your modern data-centric apps that do not need hardcore stat analysis, the use of nulls is a waste of time.

Peace & Blessings,
bmac

For true peace & happiness: www.mihr.com
Manual .sig generator v.4.3

Re:random rants

[GoofyBoy]

>More specifically, do you not design your software such that all your business logic for a particular table is located in the same place?

Reasons why you still need FKs.

1. Programmers make mistakes. If they were perfect you wouldn't need a whole lot of things besides FKs. But people are human. Having a data architect and a programmer work on relations is better. (two heads are better than one?) Getting messed up data is one of the worst things you can do to the reliablity of an application. You can fix a display problem or a "missing button" functionality but once that crappy data is in, its a pain to fix it, if you can.
2. FKs don't cost alot. Only in the cases of extreme number of fks.
3. Helps others understand the database schema. Relational databases are not object orieniented. There is a "translation" that needs to be done from the database tables to the application objects. Just because I understand the business rules and object model doesn't mean that I understand the database model.
4. The reality is that if you are talking about a serious enterprise application, the logic for the tables might not be in one place. Either because of point 3. or because there are multiple applications accessing a singlular enterprise database.
5. Programmers are hacks. Ok not all of them but some of them are. And if they see that a quick fix is to use this table in this brand new way or this field as having its own special rule, they will. This is not good because it can and will break other parts of the program or reporting. (This is an extension of point 1.)

Re:random rants

[bmac]

Well, there are two ways of looking at the typical system.

I don't see it in either of the ways you mention. I see software, regardless of architecture, as made up of three interconnected realms:

1. Spacial

This is the layout of your data types. In C/OO, it's your structs, class data members, representation for enums, etc. In a RDBMS env, it's your table schemas.

2. Temporal

This is the collection of routines that modify your data structures. In an C/OO env, it's your library fcts and class member fcts. In a RDBMS it's the "middleware". In any case, this layer is the definition of how your data can change, where each change is of one of four possible types: creation, modification, deletion and selection/reading/querying. In this layer you define first how to construct a new instance of a data structure (variable or record, depending upon env) then you define the circumstances and ways it can change.

3. Visual

While this could be put under Temporal, I find it useful to break it out into its own category, mainly because I see it as being closest to your users (the Spacial being furthest away from them). In this layer all your logic resides that concerns the presentation of both data and the visual cues (like buttons) that allow the Temporal fcts to be accessed/executed. Note that the objects that will allow this layer to be built will themselves have their own spacial & temporal layers (think of the data and fctality behind a combo box).

Now, in applying this perspective to a web application, I see the database being an (obviously) spacial layer that resides in parallel with an in-memory representation of records containing table data (this parallel rep being completely dependent upon lang being used to access the db).

So, absolutely, I can see the possibility for an object-oriented auto-mapping from an OO-type of definition to a RDBMS-type of definition of the different layers. That's what a good programmer is doing mentally and organizationally if not physically (by using a tool).

And Prevaler looks to me to be just a memory RDBMS system, but I disagree with them that the db does *not* have to be stopped to dump the memory image to persistent storage. I feel that in some db apps, you would get bad results occasionally from some race conditions, which would be very difficult to track down.

Now, to my mind, nothing is going to be revolutionary in making a new language for the spacial, temporal and visual. Why? Because computers have volatile memory, permanant storage and a visual interface (even if only on the client machine). Any application you can think of is going to reside in that flow, i.e. mem to/from perm, visual to/from mem. What will be revolutionary are new ways of constructing spacial-temporal-visual frameworks (which we call applications or programs, even of the OS variety). A sufficiently advanced program could even be spacial-layer *and* temporal-layer agnostic.

Of course, as I'm writing this, it occurs to me that databases aren't so great for expressing all the constraints on the data anyhow;

Yes, because a schema definition cannot be used to define the relations defined by the temporal layer -- it's strictly spacial. A good example of this is a table that has a field that can link to different tables via the value in one of its sibling fields. This is a purely temporal relation and while it relates to the structure of the table, the actual linkage can only be described by a "links to either table A, B, or C" constraint. To put it more succinctly, relations between tables are always defined in time; on the simplest level, it's because when added joined records, one record will always be added before another.

Peace & Blessings,
bmac

For true peace & happiness, www.mihr.com
Why use a static .sig when you can make a new one manually each time?

Re:random rants

[bmac]

I saw all your comments in this thread and you obvioulsy think too much of yourself. You still have to learn how fully exploit a *real* DBMS.

Hmm, I know what has worked for me and I *know* that my apps *never* have bugs. And that is no hyperbole. And I know that no one has *ever* written a book on how to write a bug-free app of any significant complexity. I'd love to read it, though, if/when it happens.

And, BTW, I'd really rather not come across arrogant or cocky, but text is a bad way to represent intention, and the attitude of the reader cannot be depended upon, yet it often determines how a confident opinion is interpreted. So, just for the record, my intention here is to state some beliefs my experience has ingrained and then to have content-based (as opposed to emotion-based) discussions about them. And, sure, I'm gonna fail at that last part sometimes, but I'm trying.

Ever heard something about triggers ? Or stored procedures ?

Of course I've heard of them, even tried them. But, first, I tend to not use non-standard, vendor-supplied additions to technology, especially when good ol' SQL will do the job nicely.

Technically, though, I don't use triggers because how do you error-trap an auto-exec'd fct? Sketchy at best. When will that error occur (this relates to knowing *exactly* when the trigger fct will execute). How will it get reported? If the trigger exec's before my SQL statement returns, then how do I know where the error occurred (someone here actually likes parsing SQL error statements, though I find that more error-prone then just pre-checking the failure points).

As far as stored procs go, I'd *love* to use them. To have a single list of functions be the interface to my entire persistent storage would enable an excellent interface definition. The problems are 1) that Oracle's stored proc lang is so friggin ugly that it can't be good, and 2) interfacing stored procs from a normal prog lang is too raggedy to depend on. They're just not elegant enough, and in my experience, elegance determines probability of success. And success is defined by only one thing: no bugs in a correctly coded app. Triggers and stored procs (so far) only lead to greater indeterminism and, therefore, greater chance of failure to *know* that there are no bugs.

MySQL is all fine, but prototyping in it and then porting the DB to Oracle is kind of silly IMHO.

It's not silly when the first half of the project is being written from outside a bullet-proof corporate intranet wherein the app will reside, and you have no like machine configuration to dev on. Plus, it was a challenge to seamlessly manifest the logic using manual cross-platform development techniques. It was friggin' cool, and I didn't mention that we did this with two weeks to go before my time in Finland ran out, but I still vacationed the last weekend I was there, because it was *done*.

Peace & Blessings,
bmac

Sig for me means Significant
Manual .sig generator v3.5 :-)

Re:random rants

[bmac]

1. Programmers make mistakes. If they were perfect you wouldn't need a whole lot of things besides FKs. But people are human. Having a data architect and a programmer work on relations is better. (two heads are better than one?) Getting messed up data is one of the worst things you can do to the reliablity of an application. You can fix a display problem or a "missing button" functionality but once that crappy data is in, its a pain to fix it, if you can.

Sure, we all make mistakes, but the really good programmers find their mistakes and fix them in the alpha stage (screw beta!).

2. FKs don't cost alot. Only in the cases of extreme number of fks.

Yeah, but anything is more than nothing, though, if, as one other poster suggested, they can improve query opt, they *would* be worth it then (but not as a reliability tool). I doubt this, though, because if my query is doing joins between indexed keys, and I've ordered my join properly, the optimizer *better* do it right, for all those thousands of dollars (excluding MySQL, of course, which, IMO, is *excellent*).

3. Helps others understand the database schema. Relational databases are not object orieniented. There is a "translation" that needs to be done from the database tables to the application objects. Just because I understand the business rules and object model doesn't mean that I understand the database model.

True, especially your last sentence, but any constraint put within a db scheme *by definition* will fall short of the truth of the relations fleshed out by the actual middleware. By definition. Proof is evident by virtue of the corollary being false: why can't all the middleware be implemented using constraints? It can't (and will never be). That's the job of functions, and documenting their purposes/effects is a thorny realm.

4. The reality is that if you are talking about a serious enterprise application, the logic for the tables might not be in one place. Either because of point 3. or because there are multiple applications accessing a singlular enterprise database.

Well, first you need to see my "2nd Law of Computer Programming" (in my journal) which states the direct relation between code organization and probability of success. Where the logic resides is irrelevant, IMO it is still just one app if it modifies the same data. Besides, there certainly isn't more than one design doc, right?

5. Programmers are hacks. Ok not all of them but some of them are. And if they see that a quick fix is to use this table in this brand new way or this field as having its own special rule, they will. This is not good because it can and will break other parts of the program or reporting. (This is an extension of point 1.)

Well, you state it yourself: it is *not good*. And no programmer of that caliber is going to work on my project. Not for long, at least. It would be a stipulation of his hiring and the grounds for his dismissal.

To sum up my feeling on FKs: they're a band-aid for a bullet wound.

Peace & Blessings,
bmac

For lasting peace & true happiness: www.mihr.com
MSG v3.6

Re:random rants

[GoofyBoy]

First;

>we all make mistakes

Then you have these statements that assume that programmers do not make mistakes;

>good programmers find their mistakes and fix them

and

>if my query is doing joins between indexed keys, and I've ordered my

and

>why can't all the middleware be implemented using constraints?

People make mistakes. They happen. Thats why software development is evolving. Thats is why its good to have checks and double checks. Especially at very little cost.

> IMO it is still just one app if it modifies the same data.

Not strictly. Say you have a sales/accounting which feeds data, then you have budgeting people which needs to forcast. Say that they have two different IT departments. One uses an internal one, the other thinks that the internal is crappy and outsources. They can develop two different applications/design doc/programmer teams/end-user requirements, using the same database.

And yes this does happen.

>Well, you state it yourself: it is *not good*.

Yes its not good. But it happens and sometimes its out of your control. Its like saying "I dig holes this size and only holes this size. Don't ask me to dig a hole larger because I can't do it." Instead of "digging holes" you are saying "work with bad programmers".

Another way of looking at is that your methodology is only good if its in a very specific environment. Thats easy to say; give me enough time and I can make any product perfect.

In the real world, you have crappy code, crappy data, horrible users, horrible managers and even worse timelines.

>FKs: they're a band-aid for a bullet wound.

Its a tool which can save you one day. Your main reason for not using them seems to be "programmers should be good enough" so they are not needed. But in that light, programmers should be good enough that they don't need alot of things. Why not just program in assembly, they get a huge amount of speed and should produce bug-free code. C/C++/J2EE is just a crutch for bad programmers.

If you admit that programmers make mistakes, then you really have to try and minimize them. FK are just one tool which helps.

Re:random rants

[bmac]

Not strictly. Say you have a sales/accounting which feeds data, then you have budgeting people which needs to forcast. Say that they have two different IT departments. One uses an internal one, the other thinks that the internal is crappy and outsources. They can develop two different applications/design doc/programmer teams/end-user requirements, using the same database.

You can *not* have two different design specs implemented that *modify* the same data. Read-only apps don't count. There would be no consistent data without a consistent insert/update/delete policy. And, yes, 1000 people can read the data 1000 different ways, but that doesn't factor into the stability of the data.

Your main reason for not using them seems to be "programmers should be good enough" so they are not needed.

No, my reason for not using them is that the logic that connects those two tables (in the FK relationship) will be far more complex than just "table A has a 1:n relationship with table B". And programmers *must* be good enough to implement the level of complexity that *actually* relates the two tables, or you don't have a viable app.

It seems that you are an experienced db app developer, so I ask you: how do you set up a FK relationship between a table that has a field that, depending on another integer field, can be a link to any of N other tables? Is there a standard for this? And what if one of the integer values is for a "no link at all" condition. This real-world example demonstrates the meaningless of a FK statement (that would be required) such as "table A links to tables B, C or D via field X, depending on the value in field Y".

Peace & Blessings,
bmac

For true peace & happiness: www.mihr.com
MSG 3.85 :-)

Re:random rants

[Anonymous Coward]

At least on Oracle, store proc. can be written in almost anything including C and Java (with try/catch/finally mind you) and most of triggers are part of standard SQL.

You should read a bit the manuals ... or maybe just measure yourself against real DBAs when designing large scale systems.

Re:random rants

[GoofyBoy]

>You can *not* have two different design specs implemented that *modify* the same data.

I totally agree with you, but I've just went through this situation and when the client is motivated you can't just throw out technical/"because its not clean" reasons. There are lots of things which should not happen but they do.

>how do you set up a FK relationship between a table that has a field that, depending on another integer field, can be a link to any of N other tables? Is there a standard for this?

I think something similar to this is sub/super-types. (A CHAIR and a DESK are sub types of a EXPENSIVE_OBJECT) You can lookup how its done for the db you are working with, but there is no standard SQL fixed way of handling this. But it usually always involves one FK.

Just because a technology does not fully captures a requirement doesn't mean that you should avoid it entirely. You really are doing a dis-service to your client since you are not using full use of the tools they bought/implemented.

Re:random rants

[bmac]

Just because a technology does not fully captures a requirement doesn't mean that you should avoid it entirely. You really are doing a dis-service to your client since you are not using full use of the tools they bought/implemented.

Actually, my clients are always *totally* satisfied with the results of my work, and no client I have ever worked for has given two sh*ts how I got the job done, so long as the programs worked. There are *never* any bugs in my finished product. I know it sounds ridiculous, but it's true. That's because I always use the simplest features of the chosen env and then build upon patterns that I find to work. And I test the crap out of it after every code addition.

As a further example of my disdain for the norm, I very rarely use inheritance in C++. Think that's absurd? Well, if you've read the Go4's book "Design Patterns" (truly a seminal work in CS), you would see that one of their guiding principles of oo design is "use containment over inheritance", which is where you use the parent class as a data member as opposed to as the base class. Sure, inheritance has its purpose, but you just don't use a thing because it's there, you use it for a reason after careful consideration of the other ways a purpose can be achieved and what the relative costs are. And, in my experience, FKs put into a database schema are a waste of time, whose only possible purpose would be to enhance query optimization.

Peace & Blessings,
bmac
For the secrets to life itself: www.mihr.com
MSG 3.8

Re:random rants

[Anonymous Coward]

Hmm... triggers happen when they're documented. They don't just randomly happen.

At least in Oracle, your triggers will work before/after insert, update, delete.

In other databases, like Interbase and SQL Server, you can have multiple before insert/after insert triggers, that happen in order.

At least in Oracle, stored procedures that use cursors or dynamic sql that do insert/update/deletes do so sequentially in your code. We're talking TCP here, not UDP.

The ACID model sort of requires/highly depends on it...

Re:random rants

[Anonymous Coward]

It's an Oracle quirk: "" is equivalent to null...

In most other SQL databases, "" != null, because null != anything, including null != null...

Re:random rants

[dubl-u]

I see software, regardless of architecture, as made up of three interconnected realms.

Interesting perspective.

And Prevaler looks to me to be just a memory RDBMS system, but I disagree with them that the db does *not* have to be stopped to dump the memory image to persistent storage. I feel that in some db apps, you would get bad results occasionally from some race conditions, which would be very difficult to track down.

I'm not sure what part of their literature you're referring to, but they can do what they say. One way is to leave the system open to reads but stop all writes when snapshotting. The other is to run two servers in parallel: the backup server pauses the command queue, does the snapshot, and then resumes processing the queued commands. Either way works; it just depends on your tolerance for delays.

Since Prevayler executes all writes serially, there are no race conditions in a Prevayler app.

Re:random rants

[Anonymous Coward]

Many people overlook the fact that applications change over time, but data in a database can remain there forever. You can design, and re-design your middleware a couple of times in a few years, but the database and all its objects (hopefully) will remain the same. 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. Sure, you have your website that collects or edits your data, but you also could have reports that compile your data. Or even a client application used as an administrative interface. Do you really want to code these rules into every app? The end product (reports in a lot of cases) is the most important thing. If you design your database well, it makes the reports easier to create and maintain, but also serves to ensure the accuracy of the reports. Having your middleware control what items are stored in a table is just looking for trouble IMHO.

Re:random rants

[dubl-u]

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.

From the Oracle camp

[1001011010110101]

I work for an Oracle partner company, and we tend to use the Oracle stack. I've found Oracle Designer to be quite adecuate for the task. We use it both for logical and physical data model, and for the script generation. In fact, we also use the tool for frontend generation in some cases. It also has some pretty good code repository tools, with versioning, multibranches, dependency and impact analysis and so forth.
A bit quirky, not to cheap (its part of a bundle of dev tools) but quite a nice thing for Oracle DBs.

From the MS side of the fence...

[shadowxtc]

I used to love Rational Rose... and nowadays my bloat of choice is indeed Visio. I can't say much about either as I'm a lowly developer and rarely stress them, but Visio has proved useful in some more obscure tasks now and then...

List what you need, please.

[Randolpho]

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.

Experience speaking

[borgboy]

If you really really need ERWin, you cannot afford not to have it. But I dont think most people need it, and CA aint exactly the most customer centric company out there.

ER/Studio is a close second on functionality, and wins IMO on value delivered and ease of use. It does quite a lot.

Visio...might seem to work ok at first, but will likely end up pissing you off. I came to this conclusion for both it's data modeling capabilities and the rather hamstrung UML additions.

I've used ERWin and DeZign...

[anthony_dipierro]

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

Builder.com Readers' Choice

[superyooser]

See the 2nd Annual Builder Readers' Choice Awards.

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.

Re:Builder.com Readers' Choice

[Anonymous Coward]

That's great, but this person is talking about database modelling.

DeZign Thoughts

[sidespace]

I used DeZign at a contract position this summer and have used Visio Professional in the past. I would not recommend DeZign unless your budget is very tight; it was much less featured than Visio Professional (and couldn't compare to Visio for Enterprise Architects).

The question always boils down to: what is your budget and what is your target market? If you are in the sub $500 market I would recommend Visio Professional as the tool has several other uses besides plain data modelling. Likewise, if your customer isn't knowledgeable in DB design, then using a less expensive tool will get you more short-term revenue. However if you have a large budget then learning a tool such as ERWin will not only give you better short-term results, but also increase your understanding of database theory and make your skills more marketable in the future.

Re:DeZign Thoughts

[Anonymous Coward]

Much less feature-rich than Visio? OK, if you need to do other than relational E-R diagraming. I've used Visio (2000 Pro) to try and do database modeling, and have decided that it is next to useless.

Dezign really only does one thing, but does it well. Its documentation output is great. Its database flexibility is great. Especially for the price.

Another vote for ERWin

[Future Shock]

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

Re:Another vote for ERWin

[dubl-u]

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.

Agreed! Index cards are a highly underrated technology. They're durable, easily manipulated, have a great UI, and can be used collaboratively. Plus, they don't cost $4k per user.

They have the advantage over whiteboards of being at least partially on paper should someone erase the board...

The more design I do, the less sure I get that durability is a benefit for design artifacts. After many years doing design the traditional way (with lots of documents and fancy diagrams), I switched a few years ago to doing things in the style of Extreme Programming.

It's now my feeling that any developer on a project should be able to sketch out the major objects and their relationships without much need for reference. If not, that's often a sign that the design is too confusing. And if they do need to check something, they should go right to the code: diagrams and documents get out of sync, but the source is always correct.

Of course, that means your source has to shine. It's hard work, but as Martin Fowler says, "Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

Re:Another vote for ERWin

[Anonymous Coward]

doesn't CA have an RDBMS called Jasmine?

Erm, you're a DBA?

[abulafia]

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

I hope you're trolling, and not actually deploying software.

This statement is analogous to "I have to say, I've never seen the use of backups to be needed on any filesystem I've worked with."

And unless you're doing hardcore statistical analysis, *never* allow nulls.

What the hell are you talking about? Quick - what's your employer's mother's sister's middle name? Hm, maybe that's a null value.

I won't even begin to get in to the evil I've seen applications do to work around stupid NOT NULL declarations...

To get back to the original conversation (and here I agree with the troll), I'm a big fan of textual design. I graphical tools (Visio, etc.) useful when I'm coming in to a preexisting project, but when designing, or modifying, I do prefer vi as a modelling tool. I'm not going to play a how-many-tables dick swinging game, but I will say I administer multiple complex databases for multiple clients under postgres, mysql, and oracle.

Dealing with gui tools hides a lot you need to know. ER relationships make nice pictures, but what's that trigger doing?

Re:Erm, you're a DBA?

[bmac]

I hope you're trolling, and not actually deploying software.

Sorry to disappoint, but I do, and it may just happen that you end up using my software on *your* machine, someday, God Willing :-)

This statement is analogous to "I have to say, I've never seen the use of backups to be needed on any filesystem I've worked with."

Though I'm a huge fan of hyperbole, that is an absurd analogy.

What the hell are you talking about? Quick - what's your employer's mother's sister's middle name? Hm, maybe that's a null value.

How about ""? Works for me. Empty data != null data, though one commenter noted that in Oracle (I believe) "" does actually == null. But that is absurd because null is supposed to be outside the domain of *actual* values for the field type.

I won't even begin to get in to the evil I've seen applications do to work around stupid NOT NULL declarations...

Umm, a table is a collection of data, organized into records. Each field has a range of possible values. RDBMSs allow you to give each field an optional extra value called NULL, which is a nightmare for the code that must deal with that non-value value. My apps don't "work around" the NOT NULL, they *utilize* it, to greater simplicity and, as a direct result, far greater bug-free-ness.

Re:Erm, you're a DBA?

[GoofyBoy]

>I won't even begin to get in to the evil I've seen applications do to work around stupid NOT NULL declarations...

>but I will say I administer multiple complex databases for multiple clients under postgres, mysql, and oracle.

Um... You might want to pick up an db optimizer book to see why nulls in general are evil.

Nulls are funky beasts. They don't represent anything, including other Nulls themselves. This is as far as SQL standards define them and leave the ugly details to the vendors. What this means is that operators (=, , etc) on them don't work the way it normally does with actual values.

In the case of Oracle, finding a Null value in a middle name field will result in a table scan. (Unless you do some funky things on the back end for this exact situation) Table scans is not the best way to go about things especially with large tables.

> I graphical tools (Visio, etc.) useful when I'm coming in to a preexisting project, but when designing, or modifying, I do prefer vi as a modelling tool.

In a way I agree with you, but I still use the "pretty pictures" tool because its important to communicate to others your exact change.

wide range of tools means define your requirements

[big!theory]

you need to narrow the field a bit by defining your requirements. do you want uml/high level analysis tools for people who only do analysis and never write code? or do you want something that lives closer to the actual end result, presumably a relational database?

i assume the later (because i have nothing to say about uml tools). if you do serious design work for medium complexity apps, you can immediately eliminate drawing tools like visio and toys like access.

visio is simply a clip art tool for diagrams. a fine one, but not for your task. Access is closer to what you are doing, but a toy nevertheless.

The good tools in this space target multiple databases, and should support code generation and reverse engineering.

The big dog here, with at least 60% of the market is ERWin (now owned by Computer Associates, I think). This product was the market leader for several years. However, it was sold two or three times in the span of a few years. This caused it to stagnate. ERWin today is solid but a bit dated.

Embarcadero ERStudio is much newer, and so it has a more intuitive interface, and better support for HTML publishing and the like. It has some support for reading ERWin models. But because the release in the last year/18 months is essentially a new product, you might find bugs, even crashes.

Don't underestimate the value of reading ERWin models. There are a ton of them out there, and you don't want rebuild them all from scratch.

Tools like Popkin are very complex for the highest end market. If you are a consultant, you would not want to adopt a product like this, unless you serve a very specialized market. You would never be able to introduce a product like Popkin into a smaller site. The complexity of the tool would overwhelm the complexity of the project. Despite the power of the tool, it is very dated, and benefits from a locked-in customer base. I doubt they are finding many new customer sites.

All these tools are very expensive for an individual to purchase. And they have significant annual support fees (think 3000 to license a single user copy, and 750-1000 each year thereafter, for ERStudio or ERWin).

You really need to get the support if you are going to use these products. If you find a signficant problem in mid-project, you'll have a lot of money riding on a timely fix or workaround.

Re:tsarkon reports GREASED UP YODA DOLL IN MY ANUS

[Anonymous Coward]

Perhaps you could draw a nice sequence diagram outlining your procedure?

Sybase PowerDesigner not bad

[smoon]

I've used Sybase Power Designer (an older version) before. It's not too bad and a lot cheaper then erwin. Works with lots of database servers, and prints purty wall-sized pictures assuming you've got a plotter or don't mind pasting together a bunch of sheets. Also generates SQL for building/altering databases -- typical stuff.

The only thing to look out for is which 'version' you get -- this is one of those products where you pay extra for specific features, so you'll pay anywhere from $800 or so to over $3000 or more depending on what you get and what other products they bundle in.
Good luck!

how well does it import/export DDL from X database

[1iar_parad0x]

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.

All night programming sessions

[theMerovingian]

I somtimes mark up a whiteboard, and take a digital picture before erasing it.

Never know when you are whacking a good idea...

Re:All night programming sessions

[Zathrus]

Well, we now have one "electronic" whiteboard that will happily spit out anything written on it to a PC or print it on paper, but it's (of course) in a back boardroom that's not at all useful to developers (hopefully management is getting some use out of it).

Most of our design sessions are done on the 4'x6' whiteboard in my cube (yes, I have an astoundingly large cube; there's one other person in it, but it could house 4 people very comfortably). A digital camera would be a good start. I think it was even mentioned at one point for doing things like this.

Re:All night programming sessions

[theMerovingian]

Do you have a URL for the electronic whiteboard you purchased? Does it work as advertised?

Draw things on paper, then...

[Alex Belits]

...make a pretty diagram in Xfig.

Yes, Xfig. It will let you place all the arrows and boxes with names, but it will never pretend that it understand what you are doing, and you will still have to understand your data structures, as you should.

data modeling tools

[sqlgeek]

Well, if you're designing a model that needs to be implemented across several databases (rather rare) then I'd go with ERwin. It's the tool of choice for consultants who work with many databases -- say data warehouse folk.

On the other hand, if you're working with Oracle you really want to go with Designer. It's not being terribly actively maintained, and will likely be replaced at some later point by JDeveloper, but at this point it is unsurpassed for functionality. You want to generate audit columns (created_by, creation_date, etc) on every table, no problem. You want to create before-insert and before-update triggers on every table to populate the audit columns, no problem. You get the idea. The Oracle database has amazing features that are not (yet?) ANSI standard, so use an Oracle tool to take advantage of them. Rarely does a company invest millions of dollars in a database platform and development effort, only to decide to port it to another database the next year.

I don't know DB2 nearly as well, but it too has phenomenal features (especially for terrabyte databases, though Oracle is catching up nicely) that are not ANSI standard. Again, unless you are one of the reasonably rare folk who need their code to run on any database, don't worry too much about getting "locked" in to the database your company is using and take advantages of the features you're paying for.

And in all honesty, SQL Server is coming along but is still practically a toy in comparison with the two above. You want enterprise features (scalability, availability, clustering, fail-over, etc.) in your database and SQL Server simply does not have them. Postgres is entirely suitable for use as a learning tool, but that's it. Beyond that I haven't seen a database worth installing, in all honesty.

Good luck,
Scott