Transaction Processing or Analytics?

OLTP vs. OLAP

• OLTP (Online Transaction Processing): Interactive, millisecond latency, small number of records per query (Disk seek bottleneck).
• OLAP (Online Analytical Processing): Business intelligence, high volume scans, aggregates (Disk bandwidth bottleneck).

Schemas for Analytics

While transaction processing uses diverse models, analytics often uses a formulaic approach:

Star Schemas (Dimensional Modeling)

• Fact Table: At the center, each row represents an event (e.g., a sale). Usually very wide (100+ columns) and very large (petabytes).
• Dimension Tables: Surround the fact table, representing the "who, what, where, when, how" of the event.

Snowflake Schemas

A more normalized version of the star schema where dimensions are further broken down into sub-dimensions (e.g., brand table separate from product table).