Data Modeling Standards | Modeling Data

Data Modeling standardization has been in practice for many years and the following section highlight the needs and implementation of the data modeling standards.

Standardization Needs | Modeling data:

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.

Nowadays, business to business transactions (B2B) are quite common, and standardization helps in understanding the business in a better way. Inconsistency across column names and definition would create a chaos across the business.

For example, when a data warehouse is designed, it may get data from several source systems and each source may have its own names, data types etc. These anomalies can be eliminated if a proper standardization is maintained across the organization.

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

Fact – FCT – Used for transaction tables:
e.g. Credit Card Fact – CREDIT_CARD_FCT

Cross Reference – XREF – Tables that resolves many to many relationships.
e.g. Credit Card Member XREF – CREDIT_CARD_MEMBER_XREF

History – HIST – Tables the stores history.
e.g. Credit Card Retired History – CREDIT_CARD_RETIRED_HIST

Statistics – STAT – Tables that store statistical information.
e.g. Credit Card Web Statistics – CREDIT_CARD_WEB_STAT

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

Identifier – ID – Character column that is used as an identifier.
e.g. Credit Card Identifier – CRDT_CARD_ID

Code – CD – Numeric or alphanumeric column that is used as an identifying attribute.
e.g. State Code – ST_CD

Description – DESC – Description for a code, identifier or a key.
e.g. State Description – ST_DESC

Indicator – IND – to denote indicator columns.
e.g. Gender Indicator – GNDR_IND

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

Primary Key – PK – for Primary key constraint names.
e.g. CREDIT Card Fact PK01- CRDT-CARD_FCT_PK01

Alternate Keys – AK – for Alternate key names.
e.g. Credit Card Fact AK01 – CRDT_CARD_FCT_AK01

Foreign Keys – FK – for Foreign key constraint names.
e.g. Credit Card Fact FK01 – CRDT_CARD_FCT_FK01


If you are joining in a company as a data modeler, first speak with DBAs or the software configuration management team to know the standards.

Logical Data Modeler Role | Physical Data Modeler Role

Data Modeler Role:

Data Modelers fall into two major categories of Logical or Physical Data Modelers depending upon the role they play in developing Logical or Physical data models. Based on organization needs, data modelers only do logical data modeling or physical data modeling or combination of both. Nowadays, organizations prefer to hire data modelers, who can do logical as well as physical data modeling efficiently. Logical data modelers interact with stake holders, business analysts, smart management experts (SME) and developers.
Physical data modelers interact with logical data modelers and the database team.

For logical data modeling, please refer Logical Data Modeling Tutorial and for physical data modeling, please refer Physical Data Modeling Tutorial.

Logical 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 (data warehouse data modelling).
  • Document logical, physical and dimensional data models (data warehouse data modelling).
  • Generate reports from data model.
  • Review the data model with functional and technical team.
Support & Maintenance:
  • Assist developers, ETL, BI team and end users to understand the data model.
  • Maintain change log for each data model.

Physical Data Modeler Role:

  • Create sql code from data model and co-ordinate with DBAs to create development, testing, regression and production database.
  • Check to see data models and databases are in sync.
  • Adding data base objects (like indexes, partitions in oracle database) for performance.
  • Generating reports.


  • Based on the client’s requirements, the Logical/Physical data modeler may be asked to do PL/SQL coding, ETL process, data profiling, data analysis, DBA activities and evaluation of the software also.
  • In US, the Logical/Physical data modeler’s duty and responsibility may change due to demand/supply, regulations from USCIS/Department of Labor etc.