Data Modeling in SQL

Thomas Alex Mathew

Insight Data Science

2020-07-21

What is Data Modeling ?

• A data model organizes different data elements
• real-world entity properties
• standardizes how they relate to one another
• Governs how you data is stored in databases
• Any app/website/system is just a fancy layer over a database
• Not true for static apps/webpages

What is Data Modeling ?

Twitter Data Model

Why is Data Modeling Important

• Dictates the performance/success of any system
• Transactional performance
• Analytical performance

​

Architecture of a typical data-heavy system

Production DBs

OLTP

Analytic DBs

OLAP

APIs

Android

WWW

iOS

📸

Reporting/Business Intelligence (BI)

Data Warehouse

Running the business

OLTP Modeling�(normalized)

Relational Data Model (OLTP)

• Very useful for transactional data
• Focus on data normalization
• Ideal for updating data, minimizing storage costs and maintaining data integrity

Data Normalization

• Edgar Codd ~1970
• A schema is categorized into 3 Normal Forms
• Highly normalized data has many advantages

​

Link

Link

Link

First Normalization Form (1NF)

You can’t handle the key!

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key

First Normalization Form (1NF)

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key

You can’t handle the key!

First Normalization Form (1NF)

You can’t handle the key!

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key

First Normalization Form (1NF)

You can’t handle the key!

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key
• Complies with 1NF if AND only if (cust-id, tel-num) is a composite key

1NF compliant

1NF noncompliant

First Normalization Form (1NF)

You can’t handle the key!

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key

First Normalization Form (1NF)

You can’t handle the key!

• Contains only atomic values
• Column values should be indivisible
• No repeating groups
• Similar attributes not repeated
• Identify each set of related data with a primary key

normalize

Second Normalization Form (2NF)

You can’t handle the key!

• It is in first normal form(1NF)
• All non-key attributes are fully functional dependent on the primary key

Second Normalization Form (2NF)

You can’t handle the key!

• It is in first normal form(1NF)
• All non-key attributes are fully functional dependent on the primary key

Second Normalization Form (2NF)

You can’t handle the key!

• It is in first normal form(1NF)
• All non-key attributes are fully functional dependent on the primary key

normalize

Third Normalization Form (3NF)

You can’t handle the key!

• It is in second normal form(2NF)
• There is no transitive functional dependency
• IF A is functionally dependent on B

and B is functionally dependent on C�THEN A is transitively dependent on C (via B)

Third Normalization Form (3NF)

You can’t handle the key!

• It is in second normal form(2NF)
• There is no transitive functional dependency
• Genre ID is functionally dependent on Book ID

AND Genre Type is functionally dependent on Genre ID��THEN Genre Type is transitively dependent on Book ID

Third Normalization Form (3NF)

You can’t handle the key!

• It is in second normal form(2NF)
• There is no transitive functional dependency

normalize

Good Approximation

"[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key"

- Bill Kent

​

You can’t handle the key!

How to Data Model

• There are 3 layers to it:
• Conceptual
• Logical
• Physical

You can’t handle the key!

How to Data Model

• There are 3 layers to it:
• Conceptual Entities (along with attributes)
• Logical Attributes with data-type
• Physical DB specific model

You can’t handle the key!

How to Data Model

• Conceptual Model
• Data through business eyes
• Important relationships
• Few identifiers or candidate (PK candidate) keys
• The 3 basic tenants of Conceptual Data Model are:
• Entity: A real-world thing (singular noun)
• Attribute: Characteristics or properties of an entity
• Relationship: Dependency or association between two entities

You can’t handle the key!

How to Data Model - Conceptual

You can’t handle the key!

sale

How to Data Model - Conceptual

You can’t handle the key!

sale

How to Data Model

• Logical Model
• Defines the structure of the data elements
• Modeling structure remains generic

You can’t handle the key!

How to Data Model - Logical

How to Data Model

• Physical Model
• Database specific implementation of data model
• Define primary-keys, foreign-keys, indexes etc

You can’t handle the key!

How to Data Model - Physical

How to Data Model - RECAP

• Entities
• Attributes
• Refine attribute elements (data types)
• Check if data model is normalized
• Apply DB specific model

OLAP Modeling�(denormalized)

Resources

• “The Data Warehouse Toolkit”, Kimball
• https://www.kimballgroup.com/data-warehouse-business-intelligence-resources
• https://www.coursera.org/specializations/data-warehousing
• https://www.google.com

​

Dimensional Data Modeling (denormalizing)

• Highly normalized data not ideal for analytics
• Lots of joins, joins are hard
• Need to track data changes over time
• Dimensional Data Modeling
• Design tables for analytical queries
• Fewer joins
• Can use analytical databases
• Answer Questions Like
• How many people rode a Lyme scooter yesterday?
• What is the total volume of sales at Macy’s the week of 1/1/19?
• Compute Metrics Like
• Daily Active Users
• Gross Bookings

Star Schema

• Central fact table
• Many smaller dimension tables
• Fact table consists of foreign keys to dimension tables and recorded facts
• Dimension tables consist of primary key and attributes describing fact table row
• Easily extensible (i.e. new facts and dimensions can be added with no effect on existing reports)

Snowflake Schema

• Semi-normalized star schema: some dimension tables reference other dimension tables

Building a Dimensional Data Model

Identify:

1. Business Process
2. Grain
3. Dimensions
4. Facts

The Grain

The grain is the resolution of a single row in the fact table:

• It’s an important decision
• Once decided, must not be violated!
• Start with the most atomic grain
• A “beep” at the grocery store checkout
• Wider valid range of queries
• Each proposed grain results in a different physical fact table
• Don’t mix grains!

Dimensions

• The who (dim_users), what (dim_messages), where (dim_cities), when (dim_dates), why, and how
• Dimensions define how users will filter on your table
• Dimensions depend on the grain

Dimension Examples

What are other good dates/cities attributes you could think of?

Conformed Dimensions

A conformed dimension is a dimension table that is shared by two or more fact tables.

• Tables like “dim_date” are unlikely to be dependent on business process
• Dimension tables can be really expensive to populate
• Think of a “dim_users” type table at Facebook ~2 billion users
• Promotes conformity across fact tables

Slowly Changing Dimensions

• Data in dimensions usually changes slowly. Managing changes:
• Retain original
• Attributes do not change
• Overwrite
• Replace data in place
• Append
• Add a new row
• “Current” row can be identified with a start and end date or with a “is_current” flag
• Snapshot
• Have a separate dimension table for each DATEID

Facts

The facts of a fact table are roughly “measurements” - things you can aggregate in some way:

• Cost of a scooter ride
• Duration for a scooter ride
• Distance for a scooter ride
• Start/end time of a scooter ride
• Rating of your scooter trip

Fact or Dimension?

• Price
• Fact
• Device (like cellphone model #)
• Dimension
• User Email
• Dimension
• Duration (of a ride, etc)
• Fact
• Location
• Depends, lat/long probably a fact while city a dimension, address could be either
• Year of education
• Dimension

Transaction, Periodic Snapshot and Accumulating Periodic Snapshot Tables

• Transactional
• Grain is “one row per transaction”
• Example: a single scooter trip
• Periodic Snapshot
• Picture of the moment
• Example: number of scooter rides for a particular city and month
• Accumulating Snapshot
• The grain summarizes the measurement events occurring at predictable between beginning and end of a well defined process.
• Example: order fulfilment