What is Online Transaction Processing (OLTP) Schema?

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)

OLTP Schema

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'
<<< Dimensional Modeling tutorial – OLAP, data warehouse designWhat is a Dimension Table in the Dimensional Modeling? >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .