Creating data models

Databases and Data Analysis

Databases, and data models, consists of several tables linked to each other via primary key / foreign key relationships. The primary key is represented by one or several columns which uniquely identify a table row. The foreign key is the primary key of a second table connected with the first one. The following simplified 2 table examples represent purchase orders, and suppliers of a book store:

• OrderID is the primary key of the Purchase Orders table.
• The Subtotal column in the Purchase Orders table is an example of calculated column, dynamically calculated by the system based on item prices, and quantities. For performance reasons these calculations typically take place on the server side, e.g. using a server programming language, e.g. PHP in case of Web Development, or using Database Management System languages such as SQL (Structured Query Language). For the example below we have used JavaScript to perform these calculations. JavaScript is a programming language, complementing "higher level" HTML code on the Web client side, that is, JavaScript calculations don't directly interact with the server, don't store or change information in the server database, cannot install viruses on your computer, etc. Python, Java, or PHP can do such mischief, because they operate on the server side.
• The Purchase Orders and Suppliers tables are connected to each other through the SupplierID column.
• SupplierID is the primary key of the Suppliers table, and acts as foreign key in the Purchase Orders table. The name of the column containing the supplier ID in both tables doesn't need to be the same. It is the IDs themselves that are linked to each other.
• The Purchase Orders table is a typical example of Transactional Data, that is, data which changes very often (Bewegungsdaten in German).
• The Suppliers table is a typical example of Master Data, that is, data which doesn't change very often (Stammdaten in German).

ERP, CRM, and Business Intelligence systems are often interested in aggregations of key figures (Kennzahlen) in transactional data, e.g. aggregated revenues per year, country, or product. These aggregations usually take place at the column level, and not at the row level of a table. That's why Business Intelligence systems, and SAP HANA, are optimized for column store calculations. Master Data analysis takes place at the row level, and is interested in characteristic combinations (Markmale) of fields, e.g. qualitative information about suppliers, customers, patients, etc. Database indexes help to efficiently find data in big tables, like the indexes of a book.

Purchase Orders

Suppliers