Sunday, March 9, 2014

Where Does the Line Between Data Modeler and DBA Fall? | LinkedIn Group: InfoAdvisors Members

Follow the LinkedIn discussion

My comment 

I agree with your [Karen Lopez] "State of the Union" of Data Modeling as described in your post related to this discussion. Leaving possible and probable reasons aside that led to that state, I will here focus on your question.
According to my observations, organizations have lost (and some never adopted) the technique of targeted denormalization which bridges the gap between the logical data model and the physical database design and thus brings Data Modelers and DBAs together.
Based on a 3-level approach (I skip to discuss the steps that lead to a Logical Data Model), responsibilities of Data Modelers, Developers and DBAs can be assigned as follows:
  • Logical Data Model: developed by Data Modelers / Data Administrators
  • Transition Model: created by Developers and DBAs to denormalize the Logical Data Model according to the requirements of the application (create physical tables, views, indexes etc.)
  • Physical Database Model: based on the Transition Model, DBAs add physical parameters allowing to completely generate the DDL for the (production) database
To be consequently followed by Data Modelers, Developers and DBAs, the above 3-level approach does not only need to be advised and backed by senior management, but it requires a data modeling tool that will make it "unattractive" for anyone to seek "practical shortcuts". To be suitable for that approach, the data modeling tool e.g. has to
  • Support denormalization of Logical Data Models
  • Keep track of the lineage through the transformation process from logical tables/columns to database tables/columns
  • Offer mechanisms that automatically propagate modifications from the Logical Data Model to subsequent levels (for modifications where the methodology is algorithmic and does not require human intervention)
  • Offer mechanisms that allow to manually integrate modifications from the Logical Data Model to subsequent levels (for modifications where design decisions need to be taken)
  • Include an interface that generates the script to create / alter the database (DDL) from the Physical Database Model.

How to model a ternary associative entity with a binary constraint? | LinkedIn Group: Data Modeling

Follow the LinkedIn discussion

My comment 

Based on the information provided (and including your later remark that this is just a hypothetical conceptual model to help illustrate a concept), Option A shows the right model.

Additional relationships as of your options B and C are redundant since they do not add any semantics to the model that are not already expressed in Option A. Practical test: If you generate foreign keys, the model as of Option A will look as shown at http://www.silverrun.com/common/ternary-example-red.gif (using the notation "Information Engineering +"). 


Click on image to enlarge it

The foreign keys in the table Diploma still allow a direct navigation to the table University and the table Degree (and vice versa).