Re: ERwin and Informix

Sorry about the delay in responding. I'll make up for it by making the message excruciatingly long :) Thanks for your reply, and I think your points are interesting. I should say that I use IM (Infomodeler) almost every day, but do not use ERWin, though I have evaluated it fairly extensively in the past. I should also say that my consulting practice revolves around ORM, both in the design work I do for clients and the training I do in Object Role Modeling and Infomodeler. I thus do not qualify as a disinterested party, but I have never known many disinterested people who actually got things done :) Bill> 1) ORM is conceptual, IDEF1X is logical. ORM models are not > specifically tied to the relational structure, though they can be > mapped very effectively to relational databases. Jim>I'm not sure that many people would understand the difference you are making>between a conceptual and a logical modelling language. [snip] I doubt you mean the same thing as I do. Let me try to clarify some of the differences I am thinking of by giving an example: ORM Facts (taken from the Infomodeler verbalizer report of a sample model I made up): Facts: ------------ 1. SalesRep lives in City / City is home of SalesRep Each SalesRep lives in at most one City Examples: SalesRep '5275544321' lives in City '1' SalesRep '5275544322' lives in City '2' SalesRep '5275544323' lives in City '1' 2. SalesRep makes sales calls to City / City receives sales calls from SalesRep Each SalesRep makes sales calls to zero or more City and Each City receives sales calls from zero or more SalesRep Examples: SalesRep '527554321' makes sales calls to City '1' SalesRep '527554322' makes sales calls to City '2' SalesRep '527554323' makes sales calls to City '1' SalesRep '527554321' makes sales calls to City '5' 3. SalesRep was born in City Each SalesRep was born in at most one City Examples: SalesRep '5275544321' was born in City '10' SalesRep '5275544322' was born in City '1' SalesRep '5275544323' was born in City '10' Assume these are the only facts in the entire model. To represent this in IDEF1X you would have a Person entity with an attribute of LivesCity, and an attribute of BornCity (or some similar atribute name) and a CityPerson table with a composite primary key of Person and City. On a conceptual level, what is the difference between a LiveCity, a BirthCity, and a SalescallCity? Actually there is none, they're all just Cities, and the difference is in the roles they play with the SalesRep object. In the 3 facts above, ORM only uses two objects, SalesRep and City. In the mapping phase, the necessary logical structure will be automatically created. How about if the attribute in the IDEF1X model were simply called BirthPlace. Is that a State, a City, or a Hospital? Because the role is attached to the object in ORM, the semantic domain is very clear. The ORM construct of different roles for the same object seems much more intuitive and closer to reality to my way of thinking. In a related vein, if you asked a user to tell you some of the attributes of a typical SalesRep at his company, he might list things like height, weight, etc, but is unlikely to think of BirthCity and LiveCity as two seperate attributes of a SalesRep. I find that ORM provides a great way to get from user verbalizations of facts to a logical data model. Bill> 2) ORM is much more closely tied to natural language. [snip] Jim>There are advanatges and disadvantages to this approach. Natural >language can help people who have never done modelling before but >they can also be confusing and with large models difficult to get an >overall picture. ORM has a very powerful graphical language that accompanies the verbal representations of the facts. Infomodeler automatically draws the ORM diagrams based on the facts that the user types in. In my own work, I find the diagrams very useful to analysts, but focus on the verbalized facts when communicating with users. One of the big advantage of the facts is just what you point out about people who aren't data modelers. In most large systems, the users are not data modelers, yet the system will be a failure if the analysts don't capture user requirements and business rules and incorporate them into the database design. The big problem is one of communication, the user knows what the system needs to do from an external view, but has trouble articulating it in such a way that the analyst can construct system that will provide the required functions. ORM effectively bridges the communication gap by working from facts that can be readily understood (and verified or corrected) by users. Bill>ORM models can be populated with sample instances of fact types, >IDEF1X models cannot (that i know of). Jim>There are text areas in ERwin for populating sample instances >information along with sample queries, attribute definitions and >notes, etc. It may be that ORM models take this beyond the basic >typing in of examples and later reporting on them. ORM provides formal support for populating, and populating is actually part of the CSDP (Conceptual Schema Design Procedure) advocated by the ORM community and formalized by Halpin. In addition, Infomodeler automatically deduces fact constraints from the sample populations that are provided. Bill> 4) ORM has a more robust constraint notation than IDEF1X (e.g. >ring constraints, join constraints, set operators.) An example is >set operators between logical attributes. How do you graphically >represent the rule "A person can be given a company car, OR given a >car allowance, but not both". If they are given at most one car, or >at most one allowance amount, then car and allowance are both >attributes of the Person entity. In ORM, a simple set exclusion >constraint takes care of the rule. In ERWin, how would you >represent such a constraint? Jim>By using Sub-Types. A sub-type is a an exclusive or condition. >It's very easy to show sub-types both complete and incomplet in >ERwin inculding specifying the subtype discriminator. What would be your supertype and subtype objects in the above example (Person gets car OR money, but not both) and what would be your discriminator? I'd be interested to see how you do it. Would your subtypes in ERWin necessarily result in different tables for Person who gets Car vs. Person who gets Money? ORM also supports subtyping (including non exclusive and non exhaustive subtypes), but I don't see the need for a subtype in this situation. I would use the facts: 1)Person is issued Car Person is issued at most one Car 2)Person is given car allowance of MoneyAmount Person is given car allowance of at most one MoneyAmount I would then apply a mandatory disjunct constraint (weak OR) to the two roles of Person, and put an exclusionary constraint (to make it a strong OR) between the same two roles. The result of these constraints would be a check clause that enforces the rule in the Person table. Infomodeler will automatically generate the check clause when DDL is created. Subtyping in Infomodeler may or may not create new