Tutorials -> Dimensional Modeling Tutorial
Online Transaction Processing (OLTP) Schema
In Online Transaction Processing (OLTP), the database is designed to achieve efficient transactions such as INSERT and UPDATE. This is very different from the OLAP design.
Unlike OLAP, normalization is very important to reduce duplicates and also cut down on the size of the data. our OLTP schema may look like this:
Locations Table
| Field Name | Type |
| Loc_Id | INTEGER (4) |
| Loc_Code | VARCHAR (5) |
| Loc_Name | VARCHAR (30) |
| State_Id | INTEGER (4) |
| Country_Id | INTEGER (4) |
States Table
| Field Name | Type |
| Sate_Id | INTEGER (4) |
| State_Name | VARCHAR (50) |
Countries Table
| Field Name | Type |
| Country_Id | INTEGER (4) |
| Country_Name | VARCHAR (50) |
In order to query for all locations that are in country 'USA' we will have to join these three tables. The SQL will look like:
SELECT * FROM Locations, States, Countries where Locations.State_Id = States.State_Id AND Locations.Country_id=Countries.Country_Id and Country_Name='USA'
Next:
Location Dimension
