Star to snowflake model

You can review the information about queries for different types of databases.

In a normalized database, there are typically master tables and transactional tables. The master table data is shared by many transactional tables, or a given transactional table may have multiple columns as foreign keys for the same master table. In a query, when this type of transactional tables are joined to the same master table, the master table is repeated in the SQL query. For example:

SELECT … FROM YFS_ORGANIZATION O, YFS_PERSON_INFO PI1, YFS_PERSON_INFO PI2,
     YFS_PERSON_INFO PI3 
WHERE O.CORPORATE_ADDRESS_KEY = PI1.PERSON_INFO_KEY (+) AND
     O.CONTACT_ADDRESS_KEY = PI2.PERSON_INFO_KEY (+) AND O.BILLING_ADDRESS_KEY =
     PI3.PERSON_INFO_KEY (+)

In this example, the Person Info master table is repeated for each referencing column. If the Organization table was joined to another table that had more references to the Person Info table, you would see more Person Info tables in the query.

In the Data Model, joins are created between the entities to represent the relationships between them. Naturally, if a master table contains only one entity, and more than one reference to it is needed in a particular query, the SQL that is produced is not correct. For example:

SELECT … FROM YFS_ORDER_LINE OL, YFS_SHIP_NODE SN 
WHERE OL.SHIPNODE_KEY = SN.SHIPNODE_KEY AND OL.RECEIVING_NODE =
      SN.SHIPNODE_KEY . 

This query is not correct. Therefore, in this case, you need two entities in the Catalog that correspond to YFS_SHIP_NODE. This is done by replicating the entity Organization Distribution Nodes into Organization Receiving Nodes and Organization Ship Nodes. These are now joined individually to the Order Line's columns above.

If more entities require the same set of master tables, the master tables can be further replicated in the Catalog. For example, Organization Receiving Nodes and Organization Ship Nodes are required both by Order Line and Order Release. So there are two instances of each Node table in the Catalog.

Other tables may need to be similarly replicated. As this replication is extended for more and more master tables, the Star model of the database begins to look like a Snowflake. The following image illustrates this concept.