KnowledgeHills Logo
Follow us Follow KnowledgeHills on Twitter Follow KnowledgeHills on Facebook KnowledgeHills on LinkedIn
BPO BPM BI CMM CMS CRM DBMS ERP PM SAP Sarbanes Oxley Six Sigma
Jobs White Papers Tutorials Articles Case Studies Tools and Calculators Training Publications Contact

What is Online Transaction Processing (OLTP) Schema?

 Get free access to 6facts analytics and BI cloud for small and medium business

Your applications generate lots of usage data, whether they are web based or running on desktops. Usage data is generated when usage events happen, such as customer buying a product on your web site or sales person giving demo of product to a client or trading a stock using excel.

Using 6facts you can capture this data (facts and dimensions) and send to the cloud based data warehouse hosted at 6facts using a simple HTTP REST API calls. No need to have your own database or install any software. All your fact and dimension tables will be created on the cloud. Simply capture data, format as JSON and send to 6facts using REST API. You can see and download reports.

Click to Login as a guest and test drive 6facts

Recent RDBMS, Dimensional Modeling and Datawarehouse design Tutorials

Dimensional Modeling (DM) tutorial with OLAP and data warehouse design concepts

Share this

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 NameType
Loc_IdINTEGER (4)
Loc_CodeVARCHAR (5)
Loc_NameVARCHAR (30)
State_IdINTEGER (4)
Country_IdINTEGER (4)

States Table

Field NameType
Sate_IdINTEGER (4)
State_NameVARCHAR (50)

Countries Table

Field NameType
Country_IdINTEGER (4)
Country_NameVARCHAR (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'
Next: What is a Dimension Table in the Dimensional Modeling?     1  2  3  4  5  6  7  8  9  10     Prev: Dimensional Modeling (DM) tutorial with OLAP and data warehouse design concepts