All of us agree that structured data modeling approach will help in design, development and maintenance of database schemas effectively. However, it is important to apply a right data model pattern depending on the scenario and the nature of application for which the model is being developed. We have a few industry standard data modeling patterns such as Tree-view data model pattern, Metadata data model pattern, Third Normal Form data model pattern, and Dimensional data model pattern. In this blog, let me explain what each pattern mean to us in data modeling.
Tree-view data model pattern can be applied in a scenario where an organization hierarchy has to be represented, say for a financial institution.
Metadata data model pattern can be used while developing a data model for an ETL application. The metadata has to be maintained for Operational Data Sources (source system schema definitions, data elements to be extracted, source data formats, source system owners, source location etc), ETL process (extraction frequency, extraction methods, all transformations required in staging area) and finally end-user metadata (mapping of technical taxonomy with business taxonomy).
Third Normal Form data model pattern is more appropriate while designing the schemas for an OLTP application.
Dimensional data model pattern is applicable for developing data model using star schema and snow-flake schema in DWH application. Star schema has a fact table surrounded by a set of de-normalized dimension tables. Snowflake schema has a fact table surrounded by a set of normalized dimension tables. These schemas are applicable in building of data warehouse/data mart.
Here, an attempt has been made to highlight common data modeling patterns that we come across in our day to day practice. Appreciate your comments.