September 11, 2015 at 6:56 am
I haven't seen an ERD for the databases that I work on.
September 11, 2015 at 7:10 am
The point that keeps cropping up here is that anERD document is useful in the short term until the individual learns the system. The benefit the individual gets from the diagram diminishes in relation to the cost of maintenance.
Where the model has large scale change that requires re-learning or when staff churn is high then the cost/benefit balance is favourable.
Familiarisation breeds contempt
September 11, 2015 at 7:15 am
I use ModelRight to create the ERD. More importantly, ModelRight has the ability to generate DDL SQL for *every* feature of the model, from tables to relationships to indexes to fields...either creating or altering.
When coupled with Redgate's SQL Compare it makes database changes painless since I can modify the development model and when that's been tested migrate it automatically to QA and from there production, and I don't have to manually write any script at all! NONE. As a solo IT person that scripting feature is critical to being able to do my job at all.
The more I use ModelRight the more attached I get to it.
The takeaway here is I use the ERD to *drive* development, not just to document it. That way the documentation never gets out of sync with the development database.
An ERD tool also lets you set up custom "domains" (user defined data types) that keep fields consistent between tables. The best part? This particular tool actually does on the fly translation between domains and raw SQL data types. Basically a domain becomes a collection of datatype, default, null/not null, identity seed, etc. That's *huge*, from a productivity and reliability standpoint.
Oh, and you can customize automatic naming of things like indexes and relationships, another huge time-saver that automates consistency.
ModelRight also supports multiple workspaces that allow me to create mini-ERDs, a lot like a CAD tool lets you create different perspective views. As a communication tool it's exceptional, especially when you can use a second screen to display the view to an audience.
It's not exactly cheap ($600 for the SQL Server version) but it's one of the best tools I ever bought. Well, that and Redgate's SQL Server Tool Belt (their bundle of development tools). Honestly, with SSMS, ModelRight, Redgate's tools and Subversion it's like having two more developers on staff.
If you don't currently use an ERD tool and an ERD-driven development model you really should look into it.
September 11, 2015 at 7:40 am
Just a small note. It might be better to maintain database documentation rather than the ERD. the use of tools such as Redgates Sql Doc is far more efficient than an ERD tool that costs 10 times more. ERD tools are nice to have in a multiple database engine environment. Otherwise, go the cheap and easy route. Use SSMS , generate script option to Version Control & Gen the documentation via Redgate Sql Doc or equivalent.
September 11, 2015 at 7:44 am
Part of the problem is the common misconceptions about what a data model is. The comments have touched on some of the uses but miss the big picture. So often it is portrayed as "a pretty picture", "an academic exercise", "redundant since we can just look at the data dictionary in the DBMS", and "a barrier to real programmers because ORM solves all those problems for us".
A model is a representation of some real world thing. A data model is a depiction of the central concepts and rules of your business. I can't count how many times I've had to track down why an application violates business rules only to show it was a mismatch between what the business wanted and how the data model had been changed for the sake of "performance". Karen Lopex (Datachick) has written some great pieces on myths about data modeling that I won't repeat here.
I model COTS software to show the BUSINESS concepts that should be implemented and enforced and to identify gaps where the actual system fails to meet the requirements. This also serves as a communications tool to show non-technical people how the application views the data while hiding all the technical details and table/FK complexities.
A big part of the problem is lack of tools. Good tools are cheap compared to the cost of rework. Trying to use toys like Visio is a false economy. Imagine not giving your programmers an IDE because Notepad should be good enough. A proper modeling tool should do more than just draw boxes and lines. It should help maintain the business glossary (everyone defines Customer the same, right?), it should document data lineage and transforms, it should perform impact analysis to show all of the components effected by a change, it should enforce naming standards, it should maintain trace-ability from conceptual to logical to physical designs including relational, hierarchical, dimensional, document, or graph. A data model tool should reliably and consistently generate 90% of the DDL and DML used by the DBAs to both create and alter database objects other than application code like stored procedures and functions.
The other problem is lack of education. The majority of modelers I've met over the last 40 years only had a surface level understanding of real modeling issues or the requisite knowledge of formal logic, set theory, historical approaches, and semantics to do much more than draw pretty pictures. I've watched ridiculous religious wars (anyone else remember CODASYL-relational, Chen-Martin, Inmon-Kimball?) that serve no purpose.
This is why professional engineers object to the term software engineering. We're not a discipline, we're a craft with a really lousy apprentice-journeyman-master guild.
There are no facts, only interpretations.
Friedrich Nietzsche
September 11, 2015 at 7:49 am
At one point, maintaining the ER diagram was very important. This was published to the departmental SharePoint site, printed and hung on the wall in multiple areas, and converted to an interactive HTML on a file share for all to see and use.
Now, aside from when a problem arises, we rarely look at an ER diagram.
This may be caused by agile, a lack of budget to purchase tools such as ErWin or ER Studio, or a general perception of a lack of value (or lack of understanding) in creating one. The logical design seems to have been pushed to the background or ignored entirely.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 11, 2015 at 7:53 am
We'll Said:
"Typically when I've seen an ER diagram in a shop, it resides on a wall, printed out on multiple sheets of paper. Often there are as many handwritten additions on the pages as there are marks from a printer. The diagram is also usually out of date, even allowing for handwritten notes.
Maintaining a diagram is hard. Deciding to actually spend time working on one instead of just talking about a few entities and building them, may be harder. This week, I wanted to ask you if you're up to the challenge in your organization. "
I think of it as a visual tool. Printing out the diagram and posting on the wall is a good way to visualize the overall model, and work with other team members.
In the past, when I was working the ER ( in ERwin ), I would keep it up to data. The one on the wall might get updated when a big change happened.
It is a tool, and like all tools, different people use tools in different ways.
The more you are prepared, the less you need it.
September 11, 2015 at 7:56 am
/shrug I consider an ERD a decent high level documentation tool but if I want to really know what's going on I'll look at the database and more importantly the data in it as well as trying to find out the business rules about why that data is there.
September 11, 2015 at 8:04 am
I'm going to play devil's advocate, and ask this question: why bother with creating or maintaining the ER diagram? What purpose does it serve after the very initial design stages? Sure, it graphically shows you all the entities, but so what? When do you ever need to see that? When you are designing a new feature that requires tables and relationships, or working on enhancing some existing feature set, all that matters to you are the immediate tables relevant to your task at hand. You can always use SSMS database diagrams to generate that. In ~13 years of working with database design and development, I have never really needed the full ERD, and I would find it difficult to justify spending the time to create one.
Hakim Ali
www.sqlzen.com
September 11, 2015 at 8:16 am
hakim.ali (9/11/2015)
I'm going to play devil's advocate, and ask this question: why bother with creating or maintaining the ER diagram? What purpose does it serve after the very initial design stages? Sure, it graphically shows you all the entities, but so what? When do you ever need to see that? When you are designing a new feature that requires tables and relationships, or working on enhancing some existing feature set, all that matters to you are the immediate tables relevant to your task at hand. You can always use SSMS database diagrams to generate that. In ~13 years of working with database design and development, I have never really needed the full ERD, and I would find it difficult to justify spending the time to create one.
If all it did was make a pretty picture, you'd be right. In my case the ERD tool (ModelRight) creates and modifies the database. In other words, it does all the hard work, all I have to do is the design.
As a solo IT person it's like having another developer on staff.
I think a lot of people think of ERD as *documentation* and have the typical developer disdain for it. Properly used ERD is actually the DB equivalent of an IDE--one that creates documentation *as well as create the database*.
Dunno about you, but I *like* killing two birds with one stone!
September 11, 2015 at 8:24 am
Over the course of 11 years and 3 jobs I have never worked for an employer that had an ERD nor thought it important enough to create nor maintain one.
:crying:
September 11, 2015 at 8:27 am
hakim.ali (9/11/2015)
I'm going to play devil's advocate, and ask this question: why bother with creating or maintaining the ER diagram? What purpose does it serve after the very initial design stages?
I use ERwin as a development tool. It allows you to build out quickly a standardized data dictionary, both logical and physical schema, add PK,AK & FK quickly, and make adjustments quickly. It is a great tool to both create and maintain a database. The diagrams are just a bonus. But, you need a real tool ER, not Visio.
The more you are prepared, the less you need it.
September 11, 2015 at 8:57 am
I have never used one. When I need to understand a database I look at the metadata and trace with profiler.
September 11, 2015 at 9:26 am
Mike Sofen-356112 (9/11/2015)
Eirikur Eiriksson (9/11/2015)
My 2 Cents, when working with large and often complex schema, cluttering a detail level ERD into a single diagram does neither make sense nor serve any purpose (apart from underlining the job's complexity to the management;-) ). Maintaining a high and medium level ERDs with smaller detailed ERDs reflecting (blow-up) the HLD/MLD elements makes much more sense, is easier to maintain and more legible.😎
This is a big, real issue for anyone working on large/complex dbs. I've used ErWin, ERStudio, and Xcase modeling tools and for me and my diagramming style, Xcase's ability to create drillable sub-models on a master model page is the cat's meow. Envision having subject areas of a database like sales, inventory, customers, suppliers, etc...each of those subject areas is a sub-model on the "surface" of the main model, all FK relationships retained at the main and sub-model levels and between sub-models...it's really a beautiful thing. Allows you to print/plot any/all, etc. The other products do this, just not as elegantly, IMO.
There are quite few ways of tackling these problems but I have found that modeling tools are not one of the efficient ones.
😎
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply