ER vs ORM and Other Modeling Notations

  • Which is the dominant modeling language/notation? Is one more effective than the other and which are most often used in a real world setting? I ask because I have studied several but each claims to be the most robust. My personal belief is that ORM 2 is the most complete but I am still yet a student of the game.

    Finally, which should I invest most of my time in?

  • SqlNewJack (3/26/2012)


    Which is the dominant modeling language/notation? Is one more effective than the other and which are most often used in a real world setting? I ask because I have studied several but each claims to be the most robust. My personal belief is that ORM 2 is the most complete but I am still yet a student of the game.

    Finally, which should I invest most of my time in?

    My money is with Entity/Relationship - from the data modeling stand point nothing beats it. Just my opinion.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • From a conceptual modelling perspective ORM is more powerful and expressive. In my experience ORM is a more effective medium for communicating complex concepts and business rules than either UML or ER - especially if you need to share the models with non-technical users. It doesn't take too long to familiarise inexperienced users with the basics of ORM and it conveys more information more simply than either UML or ER can. ORM also has the potential advantage of the Rmap procedure - if you can use a tool like NORMA that supports it.

    A major obstacle to using ORM in the workplace is that software support for ORM and Rmap is extremely limited and somewhat unsophisticated. ER and UML are far more commonly used in practice and there is well-established, feature-rich software support for them.

    ORM also isn't much help when it comes to describing a database design or implementation. Rightly or wrongly, the effort and attention given to database design and implementation is often far greater than that spent on conceptual modelling. For that reason software development teams will typically find ER and UML diagrams more useful. Software developers and DBAs will generally be much more familiar with ER and UML notations and unfortunately many of them won't even have heard of ORM (worse still, they probably think that ORM means "Object/Relational Mapping"!)

    If career development is your priority then ER and UML are more essential skills to have than ORM. But if you get chance to use it then ORM is well worth knowing as well.

  • sqlvogel (3/27/2012)


    Rightly or wrongly, the effort and attention given to database design and implementation is often far greater than that spent on conceptual modelling. For that reason software development teams will typically find ER and UML diagrams more useful. Software developers and DBAs will generally be much more familiar with ER and UML notations and unfortunately many of them won't even have heard of ORM (worse still, they probably think that ORM means "Object/Relational Mapping"!)

    You have been very lucky if you have been in many places where a serious amount of time is spent on database design. Usually far more time is spent on doing conceputual modelling badly using ER or appallingly use UML. It's not at all surprising that people are more familiar with ORM meaning Object-Relational mapp{ing/er} than with it meaning Object Role Model[ling], since the former has been around rather longer and has roughly 40 times as much material on the web as the latter. Of course neither has anything like the currency or ORM = Operational Risk Management (which also has fasr more software support than Object Role Model[ling])

    If career development is your priority then ER and UML are more essential skills to have than ORM. But if you get chance to use it then ORM is well worth knowing as well.

    I wonder whether getting the keyword matches on the CV (that's all UML or ER are useful for in the context of relational schema design) gives better career enhancement that a track record of successfule database design?

    Take anything I say with a pinch of salt - I'm prejudiced against UML, and dislike ER.

    If you want to build databases that work well, you have to get the conceptual model right, and starting with the 9 steps of Halpin's CSDP (the fundamental part of Object Role Modelling) to get a refined and validated fact type diagram as a first level conceptual schema is better than anything ER will give you (which in turn is probably less damaging that anything UML will give you). There's some support in vsual studio (including VISIO) for this, I believe (personally I prefer colored pens on stacks of flip chart pads, so not too worried about software support). Getting from there to a relational schema is not excessively difficult, although there are some areas in which choices have to be made; most candidate keys are clearly implied by information in the diagram, but how far to go with null avoidance, where to provide a surrogate for a composite key, and similar questions are of course a matter of database design and not dictated by the composite schema.

    At this stage you have the ordinary tasks of database schema design: you have to list all the business rules (data integrity rules if you want to call them that) that are not implied directly by the fact diagram (there usually will be some) and use normalisation at least as far as EKNF to ensure that any of them that can be expressed as functional dependencies are enforced by constraints in the schema, and look at any that require normalisation beyond EKNF (eg to 4NF or 5NF) to see whether further normalisation will be productive or counterproductive (ORM2, I think, provides some sensible alternatives to 4NF and 5NF - there was a paper by Halpin and someone else on this a while before I semi-retired, but I never got round to reading more than a short summary of it so I don't know for certain that it's good; also, it may be that ORM2 goes further towards getting the final relational schema than I've suggested above, I am NOT up to speed on anything that recent; but of course we mathematicians have known for a very long time that BCNF, 4NF, and 5NF are all broken for certain types of information so it was nice to see the ORM people making the same point from a completely different angle). Then of course you may want to denormalise beyond EKNF (perhaps to 3NF or 2NF), particularly if you have what I call a "write-hardly-ever" database but it is generally silly not to do the normalisation first and only then denormalise if necessary.

    In my view, the best alternative to using ORM is to start from Halpin's first step (list examples of the information the database has to represent) and then use common sense to get from there to a basic table design from which you can continue with subtype incorporation into supertypes, candidate key identification, selective provision of surrogates for composite keys, primary key selection, selective null elimination, and normalisation as you would from aproper conceptual model. At least there you won't be hit by an ER model's incompatibility with the relational model, or the even larger problems of UML.

    Tom

  • Some great advice from Tom! 🙂

    In general, I've found that the language or notation or convention in which you do your modelling is less important than the process and behaviour of modelling. In my experience, the notation is usually constrained by the organisation in which I am working or the toolset in which I am working.

    Personally, I've never used UML for database modelling, so I would probably downplay that particular choice.

  • ORM is better than ER. Just wondering if anyone is running an ecommerce counterfeiter site with a Bonfire back end or merged with Bonfire?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply