A Data model is a conceptual representation of data structures (tables) required for a database and is very powerful in expressing and communicating the business requirements.
A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design.
model helps functional and technical team in designing the database. Functional team normally refers to one or more Business Analysts, Business Managers, Smart Management Experts, End Users etc., and Technical teams refers to
one or more programmers, DBAs etc. Data modelers are responsible for designing the data model and they communicate with functional team to get the business requirements and technical teams to implement the database.
Data Modeling Tools
There are a number of data modeling tools to transform business requirements into logical data model, and logical data model to physical data model. From physical data model, these
tools can be instructed to generate sql code for creating database.
Popular Data Modeling Tools
Data Modeling Tools: What to Learn?
Data modeling tools are the only way through which we can create powerful data models. Following are
the various options that we have to know and learn in data modeling tools before start building data models.
» How to install the data modeling tool on server/client?
Logical Data Model:
» How to create entity and add definition, business rule?
» How to create domains?
» How to create an attribute and add definition, business rule, validation rules like default values and check constraint?
» How to create supertypes, subtypes?
» How to create primary keys, unique constraint, foreign key relationships, and recursive relationships?
» How to create identifying and non-identifying relationship?
» How to assign relationship cardinality?
» How to phrase relationship connecting two tables?
» How to assign role names?
» How to create key groups?
» How to create sequence no's?
Physical Data Model:
» How to rename a table?
» How to rename a column,validation rules like default and check constraints?
» How to assign NULL and NOT NULL to columns?
» How to name foreign key constraints?
» How to connect to databases like MS Access, Oracle, Sybase
» How to generate sql code from data model to run against databases like MS Access, Oracle, Sybase, and Terradata etc.?
Data Modeling Development Cycle
Gathering Business Requirements - First Phase: Data Modelers have to interact with business
analysts to get the functional requirements and with end users to find out the reporting needs.
Conceptual Data Modeling – Second phase
Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the INITIAL PLANNING PHASE.
Logical Data Modeling – Third Phase
This is the actual implementation and extension of a conceptual data model. A Logical data model is the version of a data model that represents the business requirements (entire or part) of an organization
and is developed before the physical data model.
Physical Data Modeling – Fourth phase
Physical data model includes all required
tables, columns, relationships, and database properties for the physical implementation of databases. Database performance, indexing strategy, physical storage
and denormalization are important parameters of a physical model.
Database - Fifth Phase:
DBAs instruct the data-modeling tool to create SQL code from physical data model. Then the SQL code
is executed in server to create databases.
Data Modeling Standardization:
Data modeling standardization has been in practice for many years and the following section highlight the
needs and implementation of the data modeling standards.
Several data modelers may work on the different subject areas of a data model and all data modelers
should use the same naming convention, writing definitions and business rules.
Table Names Standardization:
Giving a full name to the tables, will give an idea about data what it is about. Generally, do not abbreviate the table names; however this may differ according to organization's standards. If the table
name's length exceeds the database standards, then try to abbreviate the table names. Some general guidelines are listed below that may be used as a prefix or suffix for the table.
Lookup – LKP - Used for Code, Type tables by which a fact table can be directly accessed.
e.g. Credit Card Type Lookup – CREDIT_CARD_TYPE_LKP
Column Names Standardization:
Some general guidelines are listed below that may be used as a prefix or suffix for the column.
- Key – Key System generated surrogate key.
e.g. Credit Card Key – CRDT_CARD_KEY
Database Parameters Standardization:
Some general guidelines are listed below that may be used for other physical parameters.
- Index – Index – IDX – for index names.
e.g. Credit Card Fact IDX01 – CRDT_CARD_FCT_IDX01
Steps to create a Data Model
These are the general guidelines to create a standard data model and in real time, a data model may not
be created in the same sequential manner as shown below. Based on the enterprise's requirements, some of the steps may be excluded or included in addition to these.
1» Get Business requirements.
Create High Level Conceptual Data Model.
3» Create Logical Data Model.
4» Select target DBMS where data-modeling tool creates the physical schema.
Create standard abbreviation document according to business standard.
6» Create domain.
7» Create Entity and add definitions.
8» Create attribute and add definitions.
Based on the analysis, try to create surrogate keys, super types and sub types.
10» Assign data type to attribute. If a domain is already present then the attribute should be attached to the domain.
Create primary or unique keys to attribute.
12» Create check constraint or default to attribute.
13» Create unique index or bitmap index to attribute.
Create foreign key relationship between entities.
15» Create Physical Data Model.
16» Create SQL Scripts from Physical Data Model and forward that to DBA.
Maintain Logical & Physical Data Model.
18» For each release (version of the data model), try to compare the present version with the previous
version of the data model. Similarly, try to compare the data model with the database to find out the differences.
19» Create a change log document for differences between the current version and previous version of
the data model.
Data Modeler Role
Business Requirement Analysis:
» Interact with Business Analysts to get the functional requirements.
» Interact with end users and find out the reporting needs.
» Conduct interviews, brain storming discussions with project team to get additional requirements.
» Gather accurate data by data analysis and functional analysis.
Development of data model:
» Create standard abbreviation document for logical, physical and dimensional data models.
» Create logical, physical and dimensional data models.
» Document logical, physical and dimensional data models.
» Generate reports from data model.
» Review the data model with functional and technical team.
Creation of database:
» Create sql code from data model and co-ordinate with DBAs to create database.
» Check to see data models and databases are in synch.
Support & Maintenance:
» Assist developers, ETL, BI team and end users to understand the data model.
» Maintain change log for each data model.
Data Modeling Report
From Data Modeling tools, reports can be easily generated for technical and business needs. The reports
that have been generated from logical data model and physical data model are called as business reports and technical reports respectively. Most of the data modeling tools provide default reports like subject
area reports, entity reports, attribute reports, table reports, column reports, indexing reports, relationship reports etc. The advantage of these reports is, whether they are technical or non-technical,
everybody would understand what is going on within the organization.
Logical Data Model Report:
Logical Data Model Report describes information about business such as the entity names, attribute
names, definitions, business rules, mapping information etc.
Logical Data Model Report Example:
Physical Data Model Report:
Physical Data Model Report describes information such as the ownership of the database, physical
characteristics of a database (in oracle, table space, extents, segments, blocks, partitions etc), performance tuning (processors, indexing), table name, column name, data type, relationship between
the tables, constraints, abbreviations, derivation rules, glossary, data dictionary, etc., and is used by the technical team.
Physical Data Model Report Example:
Other than default reports provided by data modeling tools, a data modeler can also create customized
reports as per the needs of an organization. For example, if an expert asks of both logical and physical reports of a particular subject area in one file (e.g in .xls), logical and physical reports can be easily
merged and reports can be easily generated accordingly. Data modeling tools provide the facility of sorting, filtering options and the reports can be exported into file formats like .xls, .doc, .xml etc.
1. The Data Models Resource Book - Len Silverston.
2. Data Modeling Essentials - Graeme Simsion
3. Data Modeling - G Lawrence sander
4. Data Modelers workbench – Steve Hoberman