The purpose of this article is to go over 4 steps to data modeling.
A data model refers to the logical inter-relationships and data flow between different data elements involved in the information world. It also documents the way data is stored and retrieved. Data models help represent what data is required and what format is to be used for different business processes.
Let’s get into some basic definitions before we apply the concepts to the case study.
I will break down the definitions of the following terms:
- Entity Relationship Diagrams
- Data Dictionaries
- Data Mapping
The goal of this article is not to provide the details of any of these techniques, but to give you a sense and familiarity of data modeling.
What are Entity Relationship Diagrams?
An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system.
ER Diagrams are most often used to design or debug relational databases in the fields of software engineering, business information systems, education and research.
Also known as ERDs or ER Models, they use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to depict the interconnectedness of entities, relationships and their attributes.
What is a Data Dictionary?
Data dictionary is a centralized repository of metadata. Metadata is data about data. Some examples of what might be contained in an organization’s data dictionary include: The names of fields contained in all of the organization’s databases.
What is Data Mapping?
In computing and data management, data mapping is the process of creating data element mappings between two distinct data models. Data mapping is used as a first step for a wide variety of data integration tasks, including:
- Data transformation or data mediation between a data source and a destination
- Identification of data relationships as part of data lineage analysis
- Discovery of hidden sensitive data such as the last four digits of a social security number hidden in another user id as part of a data masking or de-identification project
- Consolidation of multiple databases into a single database and identifying redundant columns of data for consolidation or elimination
For example, a company that would like to transmit and receive purchases and invoices with other companies might use data mapping to create data maps from a company’s data to standardized ANSI ASC X12 messages for items such as purchase orders and invoices.
Now let’s understand how to build the above using our case study.
Our case study, if you have been following my earlier blogs has been around building an online ordering system for a vegan restaurant. You can read my earlier blogs by going to the following links:
Inorder to build the class diagram, the data dictionary and the data mapping document let’s first understand the data needs.
Step 1: Document the Fields Needed on the UI
Based on the wireframes and the needs elicited from the restaurant, I have gathered the following data elements.
Name (first name, last name)id, password, New user id, New password, New verification password, address, delivery address, address of card, order id, order description, Menu item, Menu ID, Price, Type, Payment method, Credit card 9 digits, last 3, credit card expiration, Credit card company, Paypal token key, Delivery Method, Delivery ID, Report Title, Report Sales Total
The above is just a dump of the fields.
Step 2: Categorize the Fields
Based on the fields above I can see that there is a way to group these into broad categories.
Customer – Name (first name, last name), id, password, New user id, New password, New verification password
Menu – order id, order description, Menu item, Menu ID, Price, Type
Payment – Payment method, Credit card 9 digits, last 3, credit card expiration, Credit card company, Paypal token key
Delivery – Delivery Method, Delivery ID
Report – Report Title, Report Sales Total
Step 3: Start Building the Enterprise Relationship Diagram
Based on Step 2, I can see that there are 5 broad categories.
Before I get into building the Enterprise relationship Diagram let me hone in on some notation in Enterprise Relationship Diagrams.
Entity – Things, People, Places represented by a box (Group of …) aka table aka Noun – Student
Relationship – How entities act upon each other or are associated with each other. Think of relationships as verbs. Represented by a diamond. Verb – Enrolls
Attribute – A property or characteristic of an entity. Often shown as an oval or circle. Adjective – New Student
Cardinality – Defines the numerical attributes of the relationship between two entities or entity sets. The three main cardinal relationships are one-to-one, one-to-many, and many-many. A one-to-one example would be one student associated with one mailing address. A one-to-many example (or many-to-one, depending on the relationship direction): One student registers for multiple courses, but all those courses have a single line back to that one student. Many-to-many example: Students as a group are associated with multiple faculty members, and faculty members in turn are associated with multiple students.
Here are the notations for the cardinality.
Here is the end result of the case study
Now let’s move on to the topic of data mapping and data dictionaries.
Step 4: Start Building the Data Dictionary and the Data Mapping Document
In our cast study I am building one document for the data dictionary and the data mapping.
As you can see above the key is to understand the source to target transition.
In this case we have a source i.e. the UI and the target is the database.
You also need to understand whether there are any transformation rules that need to happen on the way to the data base.
This document is a living document and would need to updated as new fields are added, removed or updated.
In Conclusion:
We went through 4 steps to building and modelling the data.
Step 1: Document the Fields Needed on the UI
Step 2: Categorize the Fields
Step 3: Start Building the Entity Relationship Diagram
Step 4: Start Building the Data Dictionary and the Data Mapping Document
How do you approach your data modelling effort?
This is a book that you can read to understand the basics of data modeling.
