CHAPTER 6: PROJECT FINDINGS
6.5 Reporting Analysis System Architecture
6.5.3 Database Design
Based on the Entity-Relation Diagram, each of the entities was converted into a table. The attributes (fields) of each of the entities in the system are shown below. The database, as seen in the server contained a total of 10 tables, each having relationships with one another and containing the table structures and restrictions for the storage of all data for the reporting analysis tool system using. The system used only one relational database, named “DATABASE” with the actual information being categorised and distinguished along the different tables of the database. Tables had one primary key which was unique, might had foreign keys as common field which as relational database could be used to build up a relationship between tables. Structure concept was to have
131
more tables and fewer cases of data duplication within a database giving flexibility for changing and further development.
The first table of the database was called “KPI DATA” and it held information for every different KPI per month (Table 6:7). The user could use this table in order to retrieve updated with information about KPI results per period.
Table 6:7. KPI DATA table structure
Table: KPI DATA (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
id Text *, PK Represents the id of the record
kpi id Text *, FK
Represents the id of the Key Performance Indicator
kpi value Integer *
It is the measured value for a period
Measurement
period Text *
Represents the id of the period of the measurement
Comments Text Any comments
The second table of the database called “KPI TARGETS” included data for every different KPI targets level in a specific period (Table 6:8).. Every target level had to be established in this table for the specific period. It was linked to the table “KPI DATA” using the “kpi id” field which was common to both tables.
Table 6:8. KPI Targets table structure
Table: KPI TARGETS (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
kpi target id Text *, PK
Represents the id of the KPI Target Level
kpi target value Integer *
It is the target value for a specific period
kpi id Text *, FK
Represents the id of the Key Performance Indicator
comments Text Any comments for this KPI Target
The third table of the database called “KPI CATEGORY” presented the possible categories of every KPI based mainly on the six different perspectives which it included in the S.B.I. framework (Table 6:9). These were the four perspectives of the BSC, the employee satisfaction and the external factors.
132
Table 6:9. KPI CATEGORY table structure
Table: KPI CATEGORY (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
kpi category id Text *, PK
It is the id category of the KPI based on the perspectives of the BSC
description of
category Text *
Description of the KPI’s category
comments Text Any comments
The fourth table of the database called “DATA SOURCE CATEGORY” presented the category of the retrieved data source (Table 6:10). For example it could be the monthly report for Daimler A.G, the sales funnel report and other data sources.
Table 6:10. DATA SOURCE table structure
Table: DATA SOURCE CATEGORY (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
data source
category id Text *, PK
It is the category of the data sources
description of data
source category Text *
Description of the category of the data sources
comments Text Any comments
The fifth table of the database called “USER LEVEL” categorised the different levels for the users, which of the KPIs could be viewed or edit (Table 6:11). It had defined three main levels the salesman who could view some of the KPIs, a manager who could view all KPIs and finally administrator who could view and edit any of the KPIs.
Table 6:11. USER LEVEL table structure
Table: USER LEVEL (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
user level id Text *, PK
It is the id for user level which is defined the level of access for every user of the system description of user
level Text * Description of the user level
comments Text Any comments
The sixth table of the database called “USERS” represented the dataset of the users with their characteristics of accessing information (Table 6:12). It was linked to the table “USER LEVEL” using the “user level id” field which was common to both tables.
133
Table 6:12. USERS table structure
Table: USERS (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
user id Text *, PK
It is the user id for every unique user of the system user name
description Text * Name of the user
user level id Text *, FK
It is the id for user level which is defined the level of access for every user of the system
status Text * Active or inactive user
username Text *
username for login to the system
password Text *
Password for login to the system
comments Text Any comments
The seventh table of the database called “ACTION PLAN CATEGORY” (Table 6:13). It categorised the different action plans in four different categories based on the S.B.I. framework, training development, corporate communication, internal communication and internal procedures.
Table 6:13. ACTION PLAN CATEGORY table structure
Table: ACTION PLAN CATEGORY (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
Action Plan
category id Text *, PK It is the id for plan categories
Description Text *
Description of category action plan
Comments Text Any comments
The eighth table of the database called “ACTION PLANS” (Table 6:14). It recorded the action plans for every KPI for a specific measurement period. Every action plan was defined by its measurement period and it had a starting date by following a revised date. This structure supported to organise the revised procedure of the different action plans. It was linked to the tables “ACTION PLAN CATEGORY” using the ‘Action Plan category id’ field, “KPI DATA” using the “kpi id” field which were common to tables accordingly.
134
Table 6:14. ACTION PLANS table structure
Table: ACTION PLANS (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
Action plan id Text *, PK
It is the action plan id for every action plan for a specific measurement period Action plan
description Text *
It is the description of the action plan
Action Plan
category id Text *, FK It is the id for plan categories
kpi id Text *, FK
Represents the id of the Key Performance Indicator measurement
period Text *
Represents the id of the period of the measurement
user level id Text *, FK
It is the id for user level which is defined the level of access for every user of the system Date start action
plan Date *
It is the starting date of the action plan
Date revised action
plan Date *
It is the revised date of the action plan
comments Text Any comments
The ninth table of the database called “KPI SET” included every unique KPI with its characteristics (Table 6:15). Every KPI had a description, a category of the KPI based on the perspectives of the BSC, a frequency of measurement, data source category, a description of methods for retrieving data, the level of the KPI as main or parent, user level for each KPI categorised who had access. It was linked to the tables “KPI CATEGORY” using the “kpi category id” field, “DATA SOURCE CATEGORY” using the “data source category id” field, “USERS” using the “user id” field which were common to tables accordingly.
135
Table 6:15. KPI SET table structure
Table: KPI SET (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
kpi id Text *, PK
Represents the id of the Key Performance Indicator
kpi category id Text *, FK
It is the category of the KPI based mainly on the
perspectives of the BSC frequency of
measurement Text *
Represents the id of frequency of measurement
data source
category id Text *, FK
Represents the id of category of the data source for this specific KPI
kpi level Text *
Define if the specific KPI is main or parent
user level id Text *, FK
It is the id for user level which is defined the level of access for every user of the system method retrieved
data Text *
Short description of method of retrieving data
comments Text Any comments for this KPI
The tenth table of the database called “NOTES” included “USERS” using the “user id” field which were common to tables accordingly (Table 6:16).
Table 6:16. NOTES table structure
Table: NOTES (*= required, PK= Primary Key, FK= Foreign Key)
Name Type Key Comments
NOTES id Text *, PK
Represents the id of the Key Performance Indicator
Notes Text * Notes from the user
user id Text *, FK
It is the user id for every unique user of the system
Date entry Date * It is the entry date of the notes
comments Text Any comments for this KPI