Reply to: Data Modellng tools?

[Spacecadet]

Visual Studio 2010 has tools built in for designing, deploying and version controlling a database.
I've not used it myself but it could be worthwhile looking in to

[Freamon]

You're doing fine, I think what your missing is an Entity Relationship Diagram.

Once you have that your Dataflow will make more sense.

Then you need to link your data flows to the ERD.

Fancy tools won't help, you'll just spend more time fighting with the tools than understanding the problem.

Data Flows alone work if the data is fairly simple, but otherwise you need to start with your ERD first.

I think it's fine to use Visio for the overview and Word and Excel for the nitty gritty.

An ER diagram is what I would think of as a data model.

You can get by with Excel and Visio for a while, but if you are building complex data models you will want a proper modelling tool eventually, if only to generate the DDL properly.

[BtfBA]

Talking about relational dBs, and applying the Merise approach, I would generally recommend AnalyseSI, or a better option could be Mocodo.

If you need to represent some more object-oriented scenarii and cases (applying UML), I can think of Rational Rose.

[BlasterBates]

You're doing fine, I think what your missing is an Entity Relationship Diagram.

Once you have that your Dataflow will make more sense.

Then you need to link your data flows to the ERD.

Fancy tools won't help, you'll just spend more time fighting with the tools than understanding the problem.

Data Flows alone work if the data is fairly simple, but otherwise you need to start with your ERD first.

I think it's fine to use Visio for the overview and Word and Excel for the nitty gritty.

[Freamon]

Thanks to everyone for mulling over this.

Freamon, I have approximately 50 entities grouped into 5 'domains'.

I continued to create a data dictionary in excel and diagrams in visio but maintenance of both has come back and bitten me.


Sigh...

That's not too bad. The ones I've been looking at have hundreds of entities, maybe 70-80 per domain, and they're all in Visio.

A good data modelling tool will probably help if you are going to be doing this sort of thing in the future. It will hold all the metadata for the data dictionary and spit out reports of the data dictionary, generate the DDL etc. If you are going to do it in the future it's worth getting hold of ERWin or Embarcadero. If there's no budget for those, try one of the free ones here: Data Modelling Tools The only one I've used is FabForce DBDesigner. It's ok.

Do you have Visio Enterprise Architect or just the basic one? The basic one is lacking in some data model features IIRC.

Plenty of projects still use Excel to manage their data model.

Have a look at this spreadsheet, it will generate the DDL for you (as long as you're using SQL server). Kimball Group: Data Warehouse Training: Books (3rd "download" link from the top)

Also you mentioned data flows, what do you mean by data flows?

[cojak]

Thanks to everyone for mulling over this.

Freamon, I have approximately 50 entities grouped into 5 'domains'.

I continued to create a data dictionary in excel and diagrams in visio but maintenance of both has come back and bitten me.


Sigh...

[Freamon]

Embarcadero is also pretty good. I think ERwin is pretty "industry standard" but obviously that doesn't mean it's the best. Rational Data Architect and Telelogic System Architect (both now owned by IBM) are best avoided IMO.

Excel and Visio are still pretty heavily used and are good enough for simpler data models.

Cojak - how many entities are you modelling?

[metageek]

Tool selection

For a quick list of tools, see Data Modelling Tools.

Your choice depends on a lot of factors, including the budget, type(s) of models (see A Pyramid of Data Models « George is a Metadata Junkie, he can't help it!), how many users and models you need to support, how many and which DBMSes you need to support, whether you want to link to additional metadata (such as business processes or Enterprise Architecture stuff), and how you need to do that. How long you need to keep the models, and how you need to publish them are also important.

[xchaotic]

xchaotic! Come back. I enjoyed your post. Tell us more about apps that can handle, and presumably process, sudden and unplanned structured data entered in scalar input fields.

Depends how you define it, but both Google and Mathematica/Wolfram can now process natural language queries.

In my case, I often use XML or a similar semi-structured approach so that when a an inevitable change in the business comes, it doesn't require rewriting the core of your app.

There is a good reason that loads of (often crappy, but still) business apps were conceived in tools like M$ Excel - very often there are no resources to procure a whole software development process just to write a small tool.

The problem is when that software grows beyond a simple 'fix' used by one person and gets used in production by whole departments - I think once it's proven to work, it's time to write real software and often an Excel prototype is one of the way to document the requirements for it...

[beercohol]

i hated Erwin when I used it, to be fair I was using it to reverse engineer a largish DB but it was a pain and kept crashing, I finally got my ERD but never used it again.

I know what you mean. The other problem with ERDs is that they are supposed to be sufficiently highly abstracted from the low-level technical details that they can be used to present and verify the data requirements with the business. An ERD starts off that way, but very quickly looks messy and confusing to the non-techy. Like, why do they have to know about cadinalities nomenclature with the crows-feet, or intersection entities that handle many to many relationships?

Why not just show a relationship with an arrow, and many-to-many relationships with and arrow that points both ways. Simplifications like these don't harm us in our technical analysis, but they certainly do help the client see a real business information model (rather than a plate of spagetti).

I could go on... Like subtype entites. Why not place them neatly inside their supertypes instead of at the end of the chevron thingy. It goes on... However, the proper reason for the conceptual data modeling activity is to make sure we are building software that handles the client's true information requirements. That requires collaboration with the people who are running the business and understand it, capturing what they really do and helping them identify what they really need. So we sort of shoot ourselves in the foot if we can't present our data modelling analysis back to them in a way they can understand.

This is another reason why ERD has splintered into several flavours over the years - mostly in attempts to increase its readability.

[2BIT]

i hated Erwin when I used it, to be fair I was using it to reverse engineer a largish DB but it was a pain and kept crashing, I finally got my ERD but never used it again.

[beercohol]

xchaotic! Come back. I enjoyed your post. Tell us more about apps that can handle, and presumably process, sudden and unplanned structured data entered in scalar input fields.

[Spacecadet]

I am interested in looking into the NoSQL movement to see what's next after 'relational' DBMSs, just haven't had the time.

I don't see the NoSQL movement as being a replacement for Relational Databases.
The RDBMS method is great for tightly structured data, there will always be a need for this due to the level of control it gives you over the data plus the absolute predictability of the outputs, given the absolute nature of the data.

IMO NoSQL is great for less tightly controlled, high volume data which is liable to frequent data model changes and exposes complex hierarchies of events/relationships which can be awkward to model in a RDBMS system without using recursion

[beercohol]

Or you drop the whole pointless exercise and dump the whole data as XML, noSQL or RDF triples.

I am interested in looking into the NoSQL movement to see what's next after 'relational' DBMSs, just haven't had the time.

Eventually a car from abroad came - the validation was loosened to allow any text.

I've got nothing against foreign cars, particularly, but loosening the input constraints on a field because the data model had a badly classified car type? Not sure that's a good idea.

As now it was loose some gurds started registering mopeds, bicycles, all kinds of tulip using the now free form text field - as there was no other place to put it.

Predictable spilled milk. We sort of saw it coming though, didn't we?

as long as users don't try to put unexpected, structured data in your flat row (hint: that will happen always)

Always? As long as the app is well designed and models the client data requirements correctly, we haven't run into that problem yet.

how does your 7th form normalisation deal with "Real Life(™)"?

It ain't MY 7th form normalisation. Are you nuts?

Now, dammit, thats enough. I want you on my team!!

[Spacecadet]

Consider a simple (real life) scheme: registering cars parked in a garage.
Initially it was simple enough - registration numbers were used
Eventually a car from abroad came - the validation was loosened to allow any text.
As now it was loose some gurds started registering mopeds, bicycles, all kinds of tulip using the now free form text field - as there was no other place to put it.
I guess one could have done a very long list of fields to classify an object stored in a garage - some people stored shopping carts, how does your 7th form normalisation deal with "Real Life(™)"?

Bit of a crap example really, the vehicle registration would either have to be free text or have a DVLA lookup to cope with personalised number plates.
10-12 characters limited to a-Z, 0-9 would be enough for any foreign cars (foreign car registrations may contain a dash - but this could be ignored)
The guards should have been told to stop ******* about and trying to register bicycles on a system made for recording cars which needed parking in the garage.