Joe Thompson | Data Done Right

Overview

I focus on relational databases. There are two flavors of these and they are designed based on the end product.

The first type of database design is for a transactional database, called OLTP or Online Transactional Processing. This database powers an app or website. These databases have lots of read and write queries.

The second type of database design is for analytical and reporting purposes. This is called OATP or Online Transactional Processing and powers a data warehouse and business intelligence. These databases write once when data is added but after that mostly read. They are also aggregated for reporting.

Key Concepts

The most important attributes of database design.

Design for Enterprise

Design must consider factors outside of just this system. Data types, standards, data quality, integration all affect database design as it is upstream to some and downstream to other systems. Its better to do it right once than to go back and fix it later.

Normalization

Normalization splits data in relational databases into logical tables. They are related by one of three relationship types. One to one, one to many or many to many. Normalization is typically used in transactional systems.

Indexes

Speeds up queries by adding an index on an unique field. Similar to how the phone book is alphabetic so it is easier to find. If you are looking for my name of Thompson and you are at letter K, you keep going. If you are at V go back a bit.

Scalability

I always consider how will this database grow over time. It is much easer to design the database for scale from the get go than having to do it after the fact.

Star Schema

One of two popular data warehouse schemas. It is a de-normalized schema where a fact table is surrounded by dimensions tables that give the facts context and runs faster queries. It gets its name because the schema looks like a star.

Snowflake Schema

The second popular database schema for data warehouses. It is normalized so queries take longer than a star schema but does not need to be denormalized on import.

Database Design