Data Modeling: A Crash Course

The healthcare industry is making great steps towards using IT to provide better healthcare outcomes, increase patient safety and attempt to reduce costs.  Behind every one of these initiatives should be a well thought out IT system.  The backbones of these systems are databases.  But how does a HCO or software company start out creating this?  It all begins with system analysis and design and this cannot be done without data modelling.  Here is your crash course to data modelling and why it is important.

Data Modeling

In the data modelling process there are three items that must be completed for success.  These are Conceptual Data Models, Logical Data Models and Physical Data Models.  In this writing I will explain what data modelling is briefly, what each of these models are and why they are important to the development phase of a software system.  I will also be using an example of new patient check-in system for Dr. Model’s office that allows patients to enter demographic data upon arrival.

First, what is data modelling and why is it important?  Data modeling is, according to Margret Rouse (2010), “…the formalization and documentation of existing processes and events that occur during application software design and development.”  When data modelling analysts will use tools and techniques to translate the complexities of a system into easily understandable data-flows and processes.  These are used as the basis for construction of a database system or the re-engineering of one (Rouse, 2010).  The items that come out of this exercise show the data at various levels of granularity.  Also, by having well-documented models, stakeholders can eliminate errors before coding a new system (Rouse, 2010).

Conceptual Data Model (CDM)

This is the highest level of data modelling CDM is a vital first step when it comes to systems analysis and design.  Pete Stiglich (2010) explains that by creating a CDM  “key business entities or objects and their relationships are identified.”  For example at Dr. Model’s office, the current system of collecting information would be to let the patient fill out a form.  The designers now create a CDM, that shows the various entities that will be needed, such as a patient table, insurance company table, date table, and there could be others.  The model will then show which entities are related to one another.  So for example the patient table will have relationships to all the other tables.   It would be good to note that at this point this is a very high concept level showing what tables are related.  When creating the CDM, stakeholders will inevitably find many-to-many relationships, but these will be addressed in the logical data model (Stiglich, 2008).

Logical Data Model (LDM)

1keydata (n.d.) defines a LDM as a model that “describes the data in as much detail as possible, without regard to how they will be physical implemented in the database.”  In this model analysts will now start to identify the fields that will be in the tables and the relationships between them (1keydata, n.d.).  They will also identify primary & foreign keys, and begin the process of normalization.  As a side note normalization, as defined by Mike Chapple (n.d.), is “the process of efficiently organizing data in a database.”  Normalization is what will address the many-to-many relationships, and get rid of redundancies.  Going back to the application for Dr. Model, the analysts now include fields in the tables.  For example, the patient table will include patient first & last name, medical record number (primary key), patient sex, patient age and many other bits of demographic information.  

Physical Data Model (PDM)

Exforsys Inc. (2007) defines this data model as “the design of data while also taking into account both the constraints and facilities of a particular database management system.”  This is where analysts take the LDM and make sure that all the pieces of data are configured based on the environment.  To further explain, as in our Dr. Model case and the patient table, now we define the patient first name as a variable character type field (varchar) and state how many characters it can contain.  The age field will be defined as an integer data type.  Knowing this information from the PDM can be useful in estimating storage needs (Exforsys Inc, 2007).  Analysts must also take note that this model may be different based on the type of database management system that will be used (Oracle, MS SQL Server, MySQL, etc.) (1keydata, n.d.).

In conclusion the end result of an application is reliant on a strong data modelling process.  This process will build the foundation for the database and without it many issues can occur.  It starts off as a very high level CDM, fills in the blanks with the LDM and then makes sure that it will all fit in a neat package with the PDM.  Any HCO needs to rely on this modelling process so that it can maintain quality data for the foreseeable future.

References:

1keydata.  (n.d.).  Logical Data Model.  Retrieved from http://www.1keydata.com/datawarehousing/logical-data-model.html

1keydata.  (n.d.).  Physical Data Model.  Retrieved from http://www.1keydata.com/datawarehousing/physical-data-model.html

Chapple, M.  (n.d.).  Database Normalization Basics.  About.com.  Retrieved from http://databases.about.com/od/specificproducts/a/normalization.htm

Exforsys Inc.  (2007 April 23).  Physical Data Models.  Retrieved from http://www.exforsys.com/tutorials/data-modeling/physical-data-models.html

Rouse, M.  (2010 August).  Data modeling.  SearchDataManagement.  Retrieved from http://searchdatamanagement.techtarget.com/definition/data-modeling

Stiglich, P.  (2008 November).  So You Think You Don’t Need A Conceptual Data Model.  EIMInstitute.org, 2(7).  Retrieved from http://www.eiminstitute.org/library/eimi-archives/volume-2-issue-7-november-2008-edition/so-you-think-you-don2019t-need-a-conceptual-data-model