Chapter 8 Survey Data Entry

In this case study, we are going to design the MongoDB database for an application that stores the results of surveys using the top-down approach. The top-down approach, also known as forward engineering, means that we are starting with the conceptual data model and ending with a MongoDB database:

We will first build the conceptual data model (CDM) to capture the scope of the application through key concepts and relationships. Then, using the CDM as our starting point, we will complete the logical data model (LDM) to capture the detailed business solution. Next, using the LDM as our starting point, we will complete the physical data model (PDM) to capture the detailed technical solution, leveraging embedding, referencing, sharding, and indexing. The PDM can then be implemented as collections in a MongoDB database.

Case Study Background

You are on a project team responsible for building an application that stores the results of surveys. Any organization with any type of survey need (e.g., consumer satisfaction, market research, employee productivity) across any industry (e.g., entertainment, travel, or healthcare) is a potential user of this application. Here is a sample consumer satisfaction survey form for an organization (a restaurant in Vermont) that hired us to store their survey results:

Using this sample survey as a guide, we will start with the conceptual data model and end with a MongoDB design. In addition to this sample survey template, we are provided with two sample completed surveys:

Recall the steps to build the conceptual data model from Chapter 5, the logical data model from Chapter 6, and the physical data model from Chapter 7:

We will complete each of these 14 steps in this case study.

Conceptual Data Modeling

Recall the five steps during the conceptual data modeling phase from Chapter 5:

Step 1: Ask the Five Strategic Questions

We met with the project sponsor and her team and asked the five strategic questions. Here are the responses we received:

1. What is the application going to do? This application is going to allow any organization in any industry to store the results of any type of survey and provide the foundation for us to analyze the results (analysis will be the focus of the next project phase). We think this would be a useful application to many organizations whose surveys are still heavily paper intensive and therefore difficult to store and eventually analyze.

2. “As is” or “to be”? The application we are building is a brand-new system, and therefore we need a “to be” solution.

3. Is analytics a requirement? No, this is a data entry application and there is no need for analytics or reporting in this application, which will take place in the next project phase.

4. Who is the audience? The business analysts group needs to validate our results and the data modeling group will take our model forward to the logical data modeling phase, so the data modeling group represents our users.

5. Flexibility or simplicity? Flexibility is more important than usability. Over time survey questions will be rephrased or removed and new questions will be added. Any organization should be able to come up with any type of survey with any type of survey question and our system should be able to gracefully accommodate.

Step 2: Identify and Define the Concepts

From Question 3, we learn that we are building a data entry application, which means our data model will be relational and not dimensional. Therefore we can complete the Concept template:

Survey Concepts

Who?	What?	When?	Where?	Why?	How?
1. Organization 2. Industry 3. Survey Respondent	1. Survey 2. Survey Category 3. Survey Section 4. Survey Question	1. Survey Completion Date			1. Completed Survey

Here are the definitions for each of these concepts:

Completed Survey	One filled in survey that contains a collection of opinions from a survey respondent in reaction to an organization’s product or service.
Industry	The general sector in which an organization operates. This can include retail, manufacturing, healthcare, public welfare, education, etc. It is common for an organization to operate in more than one industry.
Organization	The company or government agency that needs the survey. Organization is equivalent to the concept of customer as the organization pays us for the survey management service we provide. We prefer the term “organization” because of the ambiguity often associated with the term “customer.”
Survey	A template of questions designed to be completed by an individual for continuous improvement of the organization. A completed survey is one filled in survey.
Survey Category	The driver for the survey such as employee satisfaction, consumer feedback, course evaluation, market research, etc.
Survey Completion Date	The date that an individual filled in the survey. If there is no date on the survey, this date field defaults to the date the survey form is received by the application.
Survey Question	The inquiry an organization uses to seek feedback. Questions can be either rating questions, fixed response non-rating questions such as Gender, or free-form text questions.
Survey Respondent	The individual who completes the survey form. We may know little (if anything) about this person other than that he or she completed a survey form. This person does not have to be a customer of the organization to complete a survey.
Survey Section	A logical grouping within the survey. For a hotel, for example, sections can include room service, dining, check-in experience, etc.

Step 3: Capture the Relationships

After meeting with the business user, we created the following sketch on a flipchart:

Notice that the concept of Survey Completion Date was too detailed for this conceptual data model, so this date will instead become an attribute on our logical data model.

Once we’ve identified the fact that two entities have a relationship, we need to articulate what that relationship looks like and make it precise. To make it precise, we find ourselves asking up to eight questions: two on participation, two on optionality, and up to four questions on subtyping. So after creating a template for our Survey example, I asked a business expert each question and checked off the appropriate answer:

Question	Yes?	No?
Can an Industry contain more than one Organization?	✓
Can an Organization operate in more than one Industry?	✓
Can an Industry exist without an Organization?	✓
Can an Organization exist without an Industry?		✓
Are there examples of Industry that would be valuable to show?		✓
Are there examples of Organization that would be valuable to show?		✓
Does an Industry go through a lifecycle?		✓
Does a Customer go through a lifecycle?		✓
Can an Organization create more than one Survey?	✓
Can a Survey be created by more than one Organization?	✓
Can an Organization exist without a Survey?	✓
Can a Survey exist without an Organization?		✓
Are there examples of Survey that would be valuable to show?		✓
Does a Survey go through a lifecycle?	✓
Can a Survey Category contain more than one Survey?	✓
Can a Survey belong to more than one Survey Category?		✓
Can a Survey Category exist without a Survey?	✓
Can a Survey exist without a Survey Category?		✓
Are there examples of Survey Category that would be valuable to show?		✓
Does a Survey Category go through a lifecycle?		✓
Can a Survey contain more than one Survey Section?	✓
Can a Survey Section belong to more than one Survey?		✓
Can a Survey exist without a Survey Section?	✓
Can a Survey Section exist without a Survey?		✓
Are there examples of Survey Section that would be valuable to show?		✓
Does a Survey Section go through a lifecycle?		✓
Can a Survey Section contain more than one Survey Question?	✓
Can a Survey Question belong to more than one Survey Section?		✓
Can a Survey Section exist without a Survey Question?	✓
Can a Survey Question exist without a Survey Section?		✓
Are there examples of Survey Question that would be valuable to show?		✓
Does a Survey Question go through a lifecycle?		✓
Can a Survey be the template for more than one Completed Survey?	✓
Can a Completed Survey use more than one Survey?		✓
Can a Survey exist without a Completed Survey?	✓
Can a Completed Survey exist without a Survey?		✓
Are there examples of Completed Survey that would be valuable to show?		✓
Does a Completed Survey go through a lifecycle?		✓
Can a Survey Respondent complete more than one Completed Survey?	✓
Can a Completed Survey be completed by more than one Survey Respondent?		✓
Can a Survey Respondent exist without a Completed Survey?		✓
Can a Completed Survey exist without a Survey Respondent?		✓
Are there examples of Survey Respondent that would be valuable to show?		✓
Does a Survey Respondent go through a lifecycle?		✓

Now that these questions are answered, we can create the most useful form.

Step 4: Determine the Most Useful Form

Recall the answer to Strategic Question #4 from the list of five strategic questions from Step 1: Who is our audience? We know that the business analyst group are our validators and the data modeling group will be our main users for this model. After having a conversation with both groups, we learn that using the traditional data modeling notation would be the most valuable form for communication.

We can therefore translate the answers to the previous set of questions into the following relational conceptual data model:

Note that from these questions, we learned that Survey goes through a lifecycle. Therefore, after a discussion with the business, we added three subtypes to the model to capture the Survey lifecycle: Pending Survey, Active Survey, and Retired Survey. We would also need to define each of these entities.

Spend a few minutes going through the answers to the questions from the prior step and see how they translate into this model.

Step 5: Review and Confirm

We now review this model with the business analyst group. This may require us making changes to our model and iterating through these steps before moving on to the next phase of logical data modeling.

Logical Data Modeling

Recall the five steps during the logical data modeling phase from Chapter 6:

Step 1: Fill in the CDM

We fill in the CDM by identifying and defining the properties of each concept, which involves completing the Properties Template and Property Characteristics Template. The Properties Template captures the properties (also known as attributes or data elements) of each conceptual entity. Here is the completed Properties Template for our survey application:

Survey Properties

	Industry	Organization	Survey	Survey Category	Survey Section	Survey Question	Survey Respondent	Completed Survey
Name	Industry Name	Organization Name		Survey Category Name	Survey Section Name	Survey Question Label Name	Survey Respondent Name
Text					Survey Section Description	Survey Question Description, Possible Answer value, Possible Answer Description		Completed Survey Free Form Response Text, Completed Survey Fixed Response Answer
Amount
Date		Organization First Survey Date						Completed Survey Date
Code	SIC Code		Survey Code	Survey Category Code
Quantity
Number		Organization DUNS Number				Survey Question Number
Identifier							Survey Respondent ID
Indicator						Survey Question Singular Response Indicator
Rate
Percent
Complex

The second template, the Property Characteristics Template, captures important metadata on each property. Here is the completed Property Characteristics Template for our survey application (Note that I removed the Source and Transform columns to make this spreadsheet more readable):

Survey Property Characteristics

Property	Definition	Sample Values	Format	Length
Industry Name	The common term used to describe the general sector an organization operations within. This is the standard description for the SIC code.	Dairy Farms Copper Ores Dog and Cat Food	Char	50
SIC Code	The Standard Industry Classification (SIC) is a system for classifying industries by a four-digit code. Established in the United States in 1937, it is used by government agencies to classify industry areas.	0241 [for Dairy Farms] 1021 [for Copper Ores] 2047 [for Dog and Cat Food]	Char	4
Organization Name	The common term used to describe the company or government agency that needs the survey.	IBM Walmart Paul’s Diner Publyk House	Char	50
Organization First Survey Date	The date when the organization first started using our services. This is not the date we received payment, but the date the organization launched their first survey.	Apr-25-2014	Date
Organization DUNS Number	Dun & Bradstreet (D&B) provides a DUNS Number, a unique nine digit identification number, for each organization.	123456789	Char	9
Survey Code	The unique and required short term the business uses for referring to a survey.	M329-A	Char	6
Survey Category Name	The common term used to describe the driver for the survey.	Employee Satisfaction Consumer Feedback	Char	50
Survey Category Code	The unique and required short term to describe the driver for the survey such as employee satisfaction.	ES CF	Char	2
Survey Section Name	The common term used to describe the logical grouping within the survey.	General Hotel Room Dining Experience	Char	50
Survey Section Description	The detailed text explaining the logical grouping within the survey. This is not displayed on the survey form.	This section contains those questions pertaining to the quality of the food and overall dining experience.	Char	255
Survey Question Label Name	What the survey respondent sees on the form. That is, the question that appears.	How was the atmosphere?	Char	255
Survey Question Description	An explanation or background on the question, which is not displayed on the survey form.	This question lets the respondent rate the quality of the decorating and warmness of the room.	Char	255
Possible Answer Value	Certain questions have a fixed response such as the Gender question for “Male” or “Female” or the “From 1 to 5, how was your meal?” question. This field stores all of the possible fixed responses.	Male Female 3	Char	50
Possible Answer Description	Certain questions have a fixed response such as the Gender question for “Male” or “Female” or the “From 1 to 5, how was your meal?” question. This field stores the meaning for each of the Possible Answer Values. This field is not displayed on the survey form.	1 means Poor 3 means Average	Char	100
Survey Question Number	A required number assigned to each question. This number is unique within a survey.	1 2	Num	3
Survey Question Singular Response Indicator	Some questions allow for more than one response. This indicator when set to “Y” for “Yes” captures that only one response is expected for this particular question. This indicator when set to “N” for “No” captures that more than one response can be chosen for this particular question.	Y N	Boolean
Survey Respondent Name	The name the survey respondent writes on the completed survey. This name is not validated and can just contain the first name, just the last name, or both.	Bob Jones Jones Bob	Char	100
Survey Respondent ID	A unique and required system-generated value for each survey respondent.	123456	Num	6
Completed Survey Free Form Response Text	Captures the responses to those questions that do not require a fixed response such as the answer to “What was your favorite dessert?”	Brownie Sundae	Char	255
Completed Survey Fixed Response Answer	Captures the responses to those questions that require a fixed response such as the gender question.	Male Female	Char	100
Completed Survey Date	The date the survey respondent completed the survey. If this date is unknown, this will be the date the survey data is received by the application.	Apr-15-2014	Date

Step 2: Normalize and Abstract (Relational Only)

The model we are building is relational, and therefore we will need to normalize and also decide where (if at all) to apply abstraction.

First Normal Form (1NF)

For first normal form, we need to move repeating attributes to a new entity and also separate multi-valued attributes. Following the question templates from Chapter 6, we asked the business analysts these questions and received answers for each:

Question	Yes?	No?
Can an Industry have more than one Industry Name?		✓
Does an Industry Name contain more than one piece of business information?		✓
Can an Industry have more than one SIC Code?		✓
Does an SIC Code contain more than one piece of business information?		✓
Can an Organization have more than one Organization Name?	✓
Does an Organization Name contain more than one piece of business information?		✓
Can an Organization have more than one Organization First Survey Date?		✓
Does an Organization First Survey Date contain more than one piece of business information?	✓
Can an Organization have more than one Organization DUNS Number?		✓
Does an Organization DUNS Number contain more than one piece of business information?		✓
Can a Survey have more than one Survey Code?		✓
Does a Survey Code contain more than one piece of business information?		✓
Can a Survey Category have more than one Survey Category Name?		✓
Does a Survey Category Name contain more than one piece of business information?		✓
Can a Survey have more than one Survey Category Code?		✓
Does a Survey Category Code contain more than one piece of business information?		✓
Can a Survey Section have more than one Survey Section Name?		✓
Does a Survey Section Name contain more than one piece of business information?		✓
Can a Survey Section have more than one Survey Section Description?		✓
Does a Survey Section Description contain more than one piece of business information?		✓
Can a Survey Question have more than one Survey Question Label Name?		✓
Does a Survey Question Label Name contain more than one piece of business information?		✓
Can a Survey Question have more than one Survey Question Description?		✓
Does a Survey Question Description contain more than one piece of business information?		✓
Can a Survey Question have more than one Possible Answer Value?		✓
Does a Possible Answer Value contain more than one piece of business information?		✓
Can a Survey Question have more than one Possible Answer Description?		✓
Does a Possible Answer Description contain more than one piece of business information?		✓
Can a Survey Question have more than one Survey Question Number?		✓
Does a Survey Question Number contain more than one piece of business information?		✓
Can a Survey Question have more than one Survey Question Singular Response Indicator?		✓
Does a Survey Question Singular Response Indicator contain more than one piece of business information?		✓
Can a Survey Respondent have more than one Survey Respondent Name?		✓
Does a Survey Respondent Name contain more than one piece of business information?	✓
Can a Survey Respondent have more than one Survey Respondent ID?		✓
Does a Survey Respondent ID contain more than one piece of business information?		✓
Can a Completed Survey have more than one Completed Survey Free Form Response Text?	✓
Does a Completed Survey Free Form Response Text contain more than one piece of business information?		✓
Can a Completed Survey have more than one Completed Survey Fixed Response Answer?	✓
Does a Completed Survey Fixed Response Answer contain more than one piece of business information?		✓
Can a Completed Survey have more than one Completed Survey Date?		✓
Does a Completed Survey Date contain more than one piece of business information?	✓

We received an answer of “Yes” to six of these questions:

• An Organization can have more than one Organization Name. We learn, for example, that a hotel chain such as Hilton can sometimes go by the names Hilton or DoubleTree or Hampton Inn. We defined Organization as the “company or government agency that needs the survey.” So we would need to have the discussion that if Hampton Inn would like a survey, would Hampton Inn be the Organization Name or would the parent company Hilton be the Organization Name? What’s very interesting is that as we start asking these normalization questions we frequently refine our existing terminology and definitions. The business may decide, for example, to store an organization hierarchy based on parent company and subsidiaries. Or the business may decide just to keep track of the organization that is paying for the survey, regardless of whether they a part of a larger organization. Let’s assume the business decides to just store the organization that is paying for the survey and not the whole organization hierarchy.
• Both Organization First Survey Date and Completed Survey Date contain more than one piece of business information. Dates contain a day, month, and year. Year contains a year, decade, century, and millennium. Sometimes we identify attributes that do contain more than one piece of information, but there may be no value in showing the individual pieces. In these cases, it is best to keep the attribute in its original state as with these dates.
• A Survey Respondent Name can contain more than one piece of business information. We might, for example, have Bob Jones as a name which contains both the respondent’s first and last name. Weigh the value gained in having the attribute broken down into its components with the effort potentially needed to break the attribute down. Sometimes the value may not be worth the effort. The business might determine some value in seeing the components, but if the task is large and highly error prone, it may not be worth the development effort. In this case, let’s assume our conversation with the business ended with breaking down this name into a first and last name.
• A Completed Survey can have more than one Completed Survey Free Form Response Text and more than one Completed Survey Fixed Response Answer. Similar to the survey containing more than one question, the completed survey can also return more than one answered question. For example, if a survey contains five free form questions and ten fixed response questions, it is very possible for the completed survey to also contain fifteen answered questions. If the survey respondent left five of the questions empty, there still would be ten answered questions. We therefore need to ensure we have a many-to-many relationship between Completed Survey and Survey Question.

Here is our 1NF model after answering these questions, breaking up Survey Respondent Name into first and last name, and adding the many-to-many relationship between Completed Survey and Survey Question:

Note that there might be additional attributes in the Survey subtypes that need to be identified.

Second Normal Form (2NF)

For second normal form, we need to ensure that each entity contains the minimal set of attributes that uniquely identifies each entity instance. In other words, each entity must contain the minimal primary key. We therefore complete the following template:

Question	Yes?	No?
Is a SIC Code needed to retrieve a single instance of Industry Name?	✓
Is an Organization DUNS Number needed to retrieve a single instance of Organization Name?	✓
Is an Organization DUNS Number needed to retrieve a single instance of Organization First Survey Date?	✓
Is a Survey Category Code needed to retrieve a single instance of Survey Category Name?	✓
Is a Survey Section Name needed to retrieve a single instance of Survey Section Description?		✓
Is a Survey Question Number needed to retrieve a single instance of Survey Question Label Name?	✓
Is a Survey Question Number needed to retrieve a single instance of Survey Question Description?	✓
Is a Survey Question Number needed to retrieve a single instance of Possible Answer Value?		✓
Is a Survey Question Number needed to retrieve a single instance of Possible Answer Description?		✓
Is a Survey Question Number needed to retrieve a single instance of Survey Question Singular Response Indicator?	✓
Is a Survey Respondent ID needed to retrieve a single instance of Survey Respondent First Name?	✓
Is a Survey Respondent ID needed to retrieve a single instance of Survey Respondent Last Name?	✓
Are a Survey Code, Survey Respondent ID, Completed Survey Date, and Survey Question Number needed to retrieve a single instance of Completed Survey Free Form Response Text?		✓
Are a Survey Code, Survey Respondent ID, Completed Survey Date, and Survey Question Number needed to retrieve a single instance of Completed Survey Fixed Response Answer?		✓

For the question on Survey Section, we learn that more is needed than just the Survey Section Name to retrieve a single instance of Survey Section Description. After additional discussion with the business, we learn that the Survey Code is needed as well in the Survey Section primary key.

We also learn that the Possible Answer Value and Possible Answer Description need more than just Survey Question Number as their primary key. We learn from talking with the business that the same Possible Answer Value can apply to more than one Survey Question. We also learn that when the Survey Question Singular Response Indicator is set to No, the same Survey Question can contain more than one Possible Answer. Therefore there is a many-to-many relationship between Survey Question and Possible Answer, which is resolved on the logical through a linking entity called an associative entity. We can then connect this associative entity to Completed Survey Response, which addresses the minimal primary key issue for the last two questions in our spreadsheet where the answer was No.

Here is our 2NF model after making the above changes to Survey Section, adding the associative entity between Possible Answer and Survey Question, and connecting Survey Question Possible Answer to Completed Survey Response:

Third Normal Form (3NF)

For third normal form, we need to remove hidden dependencies. A hidden dependency is when a property of an entity depends upon one or more other properties in that same entity instead of directly on that entity’s primary key. We therefore complete the following template for those entities that have more than two attribute:

Question	Yes?	No?
Is Organization Name a fact about any other attribute in Organization?		✓
Is Organization First Survey Date a fact about any other attribute in Organization?		✓
Is Survey Question Label Name a fact about any other attribute in Survey Question?		✓
Is Survey Question Description a fact about any other attribute in Survey Question?		✓
Is Survey Question Singular Response Indicator a fact about any other attribute in Survey Question?		✓
Is Survey Respondent First Name a fact about any other attribute in Possible Answer?		✓
Is Survey Respondent Last Name a fact about any other attribute in Possible Answer?		✓

Because there are no hidden dependencies on our data model, there are no changes to make, and the model remains unchanged from our 2NF model.

Abstraction (Relational Only)

Recall that abstraction brings flexibility to a design by redefining and combining some of the attributes, entities, and relationships within the model into more generic terms.

The general question that needs to be asked is:

“Are there any entities, relationships, or data elements that should be made more generic to accommodate future requirements?”

For our survey data model, I do not believe there are any valuable opportunities to abstract. We applied some abstraction in viewing everything on the survey form as a question. We can abstract Survey Respondent and Organization into the generic Party concept, as a Party can be either a person or organization, and each Party can play many roles such as Survey Respondent or Organization. But what value would abstracting into Party provide us here? We would lose precision and the model would become more difficult to read, and it is difficult to come up with a business scenario where the Party concept would be valuable.

Step 3: Determine the Most Useful Form

We determine that the traditional data modeling form would work well for our users, so here is our fully normalized data model, which is the same as our 2NF model:

Step 4: Review and Confirm

Previously, we identified the person or group responsible for validating the model. Now we need to show them the model and make sure it is correct. Often at this stage after reviewing the model, we go back to Step 1 and make some changes and then show the validators the model again. This iterative cycle continues until the model is agreed upon by the validators and deemed correct so that we can move on to the physical modeling phase.

Physical Data Modeling

Recall the five steps during the physical data modeling phase from Chapter 7:

Step 1: Embed or Reference

MongoDB resolves relationships in one of two ways: embed or reference. Recall the top five reasons for embedding over referencing from Chapter 7:

1. Requirements state that data from the related entities are frequently queried together.
2. The child in the relationship is a dependent entity.
3. There is a one-to-one relationship between the two entities.
4. The related entities have similar volatility.
5. The entity you are considering embedding is not a key entity.

With these five reasons in mind, we can take a pencil or marker and group entities together into collections:

Industry and Organization have similar usage and therefore can be combined. Also, a number of the survey entities can be combined. The subtypes Pending Survey, Active Survey, and Retired Survey can be rolled up into Survey. Survey Section is dependent on Survey and can be rolled up as well. In addition, because Survey Category and Survey have similar volatility, they can be combined.

Survey Respondent and Completed Survey are close to a one-to-one relationship; there appears to be little if any way to identify when the same person completes more than one survey because we know very little about the respondent. Also, most likely both Survey Respondent and Completed Survey experience little if any changes over time and therefore have similar volatility.

Survey Question Possible Answer is dependent upon Possible Answer and Survey Question, yet because there is another relationship from Survey Question, we can leave Survey Question intact and embed Possible Answer into Survey Question Possible Answer.

After applying the consolidation above and physical naming standards to the model, we have this physical data model:

Notice there are different ways of combining the entities. Normally when combining, we collapse the entity on the one side (the parent entity) into the entity on the many side (the child). For example, we collapsed Survey Respondent into Completed Survey, Possible Answer into Survey Question Possible Answer, and Survey Category into Survey.

We can also roll up the child entity into the parent entity, as we did with the subtypes as well as with Survey Section. We needed to add a Survey Type Code which has values P for Pending Survey, A for Active Survey, and R for Retired Survey.

We rolled Industry down into Organization, forcing us to come up with a maximum number of Industries an Organization can belong to (in this case three). In MongoDB however, due it its flexible structure, we can have any number of industries in the Organization collection by using an array.

Step 2: Accommodate History

Recall there are four types of Slowly Changing Dimensions (SCDs for short).  An SCD of Type 0 means we are only storing the original state and not storing changes. A Type 1 means the most current view. A Type 2 means we are storing all possible changes to the data in a collection—a complete history whenever anything changes. Type 3 means we have a requirement for some history—for example, the most current view and the previous view or the most current view and the original view.

We complete the following Collection History Template for our Survey model:

Collection History Template

	Store only the original state (Type 0)	Store only the most current state (Type 1)	Store full history (Type 2)	Store some history (Type 3)
Organization		✓
SurveyCreation		✓
Survey			✓
CompletedSurvey	✓
CompletedSurveyResponse	✓
SurveyQuestion		✓
SurveyQuestionPossibleAnswer		✓

The only collection we learned that needs full history is Survey. Most collections are Type 1, meaning only the most current. CompletedSurvey and CompletedSurveyResponse are Type 0 (as many transactions are), meaning we are just storing the original state as the transaction should never change.

Using effective dates to implement a Type 2, here is our updated physical data model:

Step 3: Index and Shard

The primary and alternate keys from our logical data model are converted into unique indexes on the physical. We can also add additional indexes for performance, called secondary or non-unique indexes. Partitioning (called “sharding” in MongoDB) is when a collection is split up into two or more parts. Vertical partitioning is when fields are split up, and horizontal is when documents are split up.

On our model there are no immediately apparent opportunities to shard. However, we did identify surveyQuestionLabelName, surveyRespondentLastName and surveyRespondentFirstName as being queried frequently, so we added non-unique indexes to each of these (Note that we added a composite index on both surveyRespondentLastName and surveyRespondentFirstName as they are frequently queried together):

Step 4: Review and Confirm

Previously we identified the person or group responsible for validating the model. Now we need to show them the model and make sure it is correct. Often at this stage after reviewing the model, we go back to Step 1 and make some changes and then show the model again. This iterative cycle continues until the model is agreed upon by the validator and deemed correct so that we can move on to MongoDB implementation.

Step 5: Implement

We’re done with our modeling, and now we can start creating documents within each of these collections!

Here is a sample document within each collection:

Organization:

  {  organizationDUNSNumber : “123456789”,

    Industry : [

      { SICCode : “3829”,

       industryName : “Restaurant” },

      { SICCode : “1289”,

       industryName : “Retail” }

      ],

    organizationName : “The Publyk House”,

    organizationFirstSurveyDate : ISODate(“2014-04-05”)

  }

Survey:

  {  surveyCode : “M329-A”,

    surveyEffectiveDate : ISODate(“2014-04-29”),

    surveyTypeCode : “A”,

    surveyCategoryCode : “CF”,

    surveyCategoryName : “Consumer Feedback”,

    surveySectionName : “Dining Experience”,

  surveySectionDescription : “This section contains those questions pertaining to the quality of the food and overall dining experience.”

  }

SurveyCreation:

  {  organizationDUNSNumber : “123456789”,

surveyCode : “M329-A”,

    surveyEffectiveDate : ISODate(“2014-04-29”),

  }

SurveyQuestion:

  {  surveyQuestionNumber : “1”,

surveyCode : “M329-A”,

surveyEffectiveDate : ISODate(“2014-04-29”),

    surveyQuestionLabelName : “Were you greeted properly?”,

surveyQuestionDescription : “This question lets the respondent rate the hostess.”,

surveyQuestionSingularResponseIndicator : “Y”

  }

SurveyQuestionPossibleAnswer:

  {  possibleAnswerValue : “3”,

surveyQuestionNumber : “1”,

possibleAnswerDescription : “Average”

  }

CompletedSurvey:

  {  surveyCode : “M329-A”,

    surveyEffectiveDate : ISODate(“2014-04-29”),

    surveyRespondentID : “123456”,

completedSurveyDate : ISODate(“2014-05-04”),

surveyRespondentFirstName : “Robbie”

  }

CompletedSurveyResponse:

  {  surveyCode : “M329-A”,

    surveyEffectiveDate : ISODate(“2014-04-29”),

    surveyRespondentID : “123456”,

completedSurveyDate : ISODate(“2014-05-04”),

possibleAnswerValue : “3”,

surveyQuestionNumber : “1”

}