.NET Hawa

Schema Modeling Techniques

on October 25, 2013

A Schema is a collection of Database objects such as Tables, views, Indexes, stored procedures etc.

Querying historical data from databases we use Dimensional Modeling Schema Design.  Dimensional Modeling can be done from both physical and logical levels.  A Dimensional Schema may be logical or physical Schema.  Dimensional Schema can take data from any table or view because it only contains facts and dimensions that have in different columns of the table.   They are 4 – types of Data Modeling Schemas:

  1. Star Schema
  2. SnowFlake Schema
  3. StarFlake Schema
  4. Many to One Relationships

Star Schema:

Star Schema designs as Fact Table at the center surrounded by Dimension tables.  Below figure shows the structure…

Star

A Star Join Query mainly joins with primary key of dimension table to foreign key of fact table.

Start Schema helps in performance optimization of queries in data warehouses.

Note:  A star schema may have no. of dimension tables but they should maintain many – to – one   relationship.

SnowFlake Schema:

SnowFlake Schema is complex schema to understand and design.  It consists of a one fact table connected to dimension tables which they connected to another dimension table.

SnowFlake

Though SnowFlake Schema occupies less space but, it requires lot of foreign keys to establish.  This results for Complex queries and degradation of performance of queries.

StarFlake Schema:

StarFlake Schema is a combination of both Star and SnowFlake Schema Models.  We need to de-normalize some dimension tables of Star schema where SnowFlake Schema dimension tables are normalized.  StarFlake Schemas Should be normalized with Outriggers to reduce any type of redundancies in the dimensions.

StarFlake

Many – to – One Relationship:

Many – to – one Relationship defines one table have many relationships, one primary key and many foreign keys.  In dimensional model Fact has many foreign key relations to dimensions which has one primary key.  In dimension model they will define in hierarchy level.

References:
http://pic.dhe.ibm.com/infocenter/idm/docv3/index.jsp?topic=%2Fcom.ibm.datatools.dimensional.ui.doc%2Ftopics%2Fc_dm_star_schemas.html
http://docs.oracle.com/cd/B12037_01/server.101/b10736/schemas.htm
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: