Sunday January 21st 2018

Categories

Insider

Archives

Flat File data model

Flat File data model

This post discusses our experience of creating a relational database for company which to store all its information Excel files.

Excel spread sheets is good for analysis purposes like pivot tables, calculations but very inefficient for data storage purpose. We cannot access data easily and compiling reports for operational departments is very complicated.

How to model Flat file into RDBMS? For that reason we have to study business process then design an entity relation diagram. Here is a scenario we aced

The data was extracted from MS Excel flat files then transformed and loaded into MS Access and modeled into RDMS format. Created tables to store Master data about all accounts including customers, suppliers, employees and entity related to Altech business. Designed tables to record financial and material transactions and coordinated with Accounts departments to create general.

In the above case we did the iterative process first excel sheet data was imported MS Access.

Below is simple example to show how falt is modeled into RDBS and its advantages for data storage.

Excel Flat file

Date Product Price Quantity Total Amount Customer
1-1-2010 Widget 1 200 3 40 ABC INC, Louisiana
1-2-201 Widget 2 3000 2 6000 XYZ Company
           

 

Products table

Prid Name Suggested Price
1 Widget 1 200
Customer Table
Cust ID Name Address
1 ABC INC Lousian, New Orleans

Sales transaction in RDBMS format

Date Product id Customer ID Qty sold Sale Price
1-1-2010 1 1 2 200

Additional tables were created to break up the one single large table.

Created customers table to hold their master data.

Created products table to hold their master data

Created transaction table business transaction like sales to customers.

Advantages of storing data in RDBMS.

1-In flat file if the customer move we have to change address in every transaction stored in Excel Sheet. In RDBMS if change address in master database of customer table we do not have to change address in every sales transaction because they transaction store the customer id which is foriegn key in transaction table.

2- Fast data entry

3- easy management of data

4- flexible reporting.

5-If want to analyze the data the data from RDBMS can be exported into Excel files for analyses purposes.