OLTP and OLAP

Database Design

Lis Sulmont

Curriculum Manager

Our motivating question:

How should we organize and manage data?

Schemas: How should my data be logically organized?
Normalization: Should my data have minimal dependency and redundancy?
Views: What joins will be done most often?
Access control: Should all users of the data have the same level of access
DBMS: How do I pick between all the SQL and noSQL options?
and more!

Our motivating question:

How should we organize and manage data?

Schemas: How should my data be logically organized?
Normalization: Should my data have minimal dependency and redundancy?
Views: What joins will be done most often?
Access control: Should all users of the data have the same level of access
DBMS: How do I pick between all the SQL and noSQL options?
and more!

It depends on the intended use of the data.

Approaches to processing data

OLTP

Online Transaction Processing

Counter kiosk for check-out

OLAP

Online Analytical Processing

Data dashboard on laptop

Some concrete examples

OLTP tasks

Find the price of a book
Update latest customer transaction
Keep track of employee hours

OLAP tasks

Calculate books with best profit margin
Find most loyal customers
Decide employee of the month

OLAP vs. OLTP

	OLTP	OLAP
Purpose	support daily transactions	report and analyze data
Design	application-oriented	subject-oriented
Data	up-to-date, operational	consolidated, historical
Size	snapshot, gigabytes	archive, terabytes
Queries	simple transactions & frequent updates	complex, aggregate queries & limited updates
Users	thousands	hundreds

Working together

OLAP and OLTP

Takeaways

Step back and figure out business requirements
Difference between OLAP and OLTP
OLAP? OLTP? Or something else?

Let's practice!

Database Design

Preparing Video For Download...