I am a student and I am developing several projects as a part of my academia.

While developing the database for one of the projects, we came across a situation where we thought about whether ERD is necessary or not. Right now, not every one of us are in agreement on developing the ERD first, and then developing database from it.

The majority of people prefers developing the database on the fly verbally according to the system requirementd on paper directly.

Now, I am strict follower of Database principles. I think the database should be developed from the ERD only. So, I just want to know the following:

  • Is the industry following these principles?
  • Am I just wasting my time on developing an ERD?
  • What are the benefits of developing an ERD?
  • ER/EER diagram shows the interrelationships between entities and it also magnifies system functionalists. – user18423 Jan 25 '13 at 13:22
  • If you want a free tool for making an ERD for SQL Server... Info can be found here... facebook.com/DataModelerTool or here... plus.google.com/108968161662966473138 – Carter Medlin Feb 13 '14 at 4:01
  • IMHO, ERD's don't capture everything important - changes in relationship over time, transaction volume, inheritance in object modeled systems, "becomes a" relationships, etc. Specifically, ERD's factor out all verbs, leaving large gaps in the database design. Imagine reading a novel consisting of nothing but nouns. I find that they are useful tools, but certainly not critical, best drawn on the backs of napkins after a nice lunch. – pojo-guy May 12 '16 at 4:41

Plain and simple, think of developing a database without an ERD as building a house without a building plan. It might be doable because you think that simply laying a brick one over another is enough to build something, however the moment somebody else takes responsibility over the project there is disaster potential.

In my experience you will not benefit much from ERDs unless you use them in conjunction with CASE tools (ERWin, MySQL Workbench etc.) which will additionally allow you to perform some really helpful operations such as forward and reverse engineering. Even without these functions having a centralized schematic of the complete database is useful because sometimes the constraints implemented in the database itself are not enough to tell the full story about the relationships between particular database entities.

Here is an example involving MySQL which, as you might know, implements several internal storage engines, most notably MyISAM and InnoDB. There are significant differences between them one of the most important one being that MyISAM doesn't support constraints. Despite that fact MyISAM is heavily used for web applications which means that the relational logic needs to be implemented either through business logic (application code) or in another way. The problem is when you forward engineer an ERD with MyISAM tables (entities) MySQL will silently ignore the constraints set by the ERD and you will end up with a database which doesn't clearly identify the nature of the relationships between entities. In other words after you develop the database layout there is no way for the code developers to implement proper business logic without an ERD.

| improve this answer | |
  • 1
    If we go all the way with your " building plan" comparison, we have to build a system and can never change it unless we have to demolish the whole thing. This is not going to work in a dynamic environment. – A-K Jan 25 '13 at 20:36
  • 1
    The purpose of the "building plan" is not to cement the development process or the project itself but rather always provide an overview of the state in which the current project is. Nobody is actually stopping anyone from adding new entities or relationships (hence making changes to the plan) but others need to know about those changes too. – brezanac Feb 6 '13 at 16:12

ERDs are really nice to have a clear picture of your database structure. Besides being an important document artefact to you project, it eases when you need to implement queries, especially joins among tables. Imagine how nice it's to have a dual monitor config, on the left side you have you ERD and on the right side your query editor. You can clearly see the relationships among tables and write your queries based on that. If your database project is quite simple, and your project doesn't require it, maybe it's ok to live without it.

| improve this answer | |

ERD will save you much more time than you think, If you do everything on the fly then you will stuck when you want to generate junction tables.

If you come across to database a few years later without ERD, you have to spend a lot of time to see what is going on in your project.

I have company and we design ERD, don't ever think about database without ERD.(actually this is my own personal experiment)

| improve this answer | |

ERDs used to be more mainstream some time ago. IMO they are more or less optional now.

Currently, some highly successful teams do not bother with ERDs at all, yet deliver excellent systems: robust, performant, and easy to maintain in the long run. This is especially true in Agile development, when we start small, with a minimalistic set of tools.

ERDs are a good way of communication, of course, but by no means the only way, or the best in all circumstances one. Some teams prefer other ways of documentation and communication.

There are no blanket rules in this industry.

| improve this answer | |
  • +1 but which other ways od documentation and communication are used in regard to databases? – miracle173 Jan 26 '13 at 16:40
  • @miracle173 A good book is: "Agile Principles, Patterns, and Practices in C#". Also I like articles on Behavior Driven Development by Dan North at dannorth.net – A-K Jan 28 '13 at 19:09
  • 1
    What do you mean by some teams prefer other ways? I think this could have ended up with plenty of down votes If was posted as an answer on Stackoverflow! – Pmpr Feb 15 '16 at 3:31

It's important to design before writing production code. It's also important to prototype; database people develop prototypes by writing SQL. (Remember what Fred Brooks said about prototypes?)

Graphical languages, of which ER is just one, have not proven to be very good at capturing all the semantics of a database in a way that's easy and simple to understand.

They also haven't proven to be very effective communication tools, except between developers. But in designing databases, the crucial communication isn't between developers; it's between developers and domain experts (between developers and business people).

Object-Role Modeling (ORM) has a graphical language, but it's based on "facts" (simple sentences). Business people can validate facts pretty easily. Business people can't easily validate an ER diagram or an ORM diagram.

| improve this answer | |
  • 1
    +1 but do you knowany figures or studies that supports your claims about the the problems of communicationg using graphical languages? – miracle173 Jan 26 '13 at 18:01
  • I'm not in academics, so I don't follow the research closely. A graphical language that fails to capture all the semantics of a database (an ERD, for example) obviously can't communicate all the semantics; that's part of Terry Halpin's research. As far as communicating with domain experts, you don't really need research for that. You just need to try to explain an ER diagram to a domain expert who has only an 8th grade education. Then compare that experience with trying to explain the sentence "Every address has one and only one ZIP code." The sentence wins every time. – Mike Sherrill 'Cat Recall' Jan 27 '13 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.