Razer Technology Solutions, Inc.

332 Gordon Drive
Exton, PA 19341
610-524-7073

No, we are not talking about the weather. If you've done any reading about Data Warehouses
or participated in data modeling, you most likely know that Star and Snowflake refer to data
model topologies. A Star Schema refers to the particular shape the entity diagram takes when
certain types of relationships exist in the data. A Snowflake design refers to the appearance of
a typical entity model used to handle business transactions.

The Snowflake topology shows up in models where more than one binary or unary data relationship
is present. We see multiple 1:m and m:n joins in the model branch out and take on a jagged semi-
symmetrical pattern reminiscent of a snowflake. These patterns show up in many different types of
business data models. We refer to these transactional databases as OLTP (On-Line Transaction Processing)
databases. Transactional data is just what it sounds like: the data about specific business transactions.
Virtually every business has some form of transactional data to store, manage, and report on.
Transactional data is normally stored for the duration of the transaction or transactions. After the
transaction(s) are completed, data may be purged or archived.

Larger businesses begin to archive their data and study it over time to assist in the decision making
process. These kinds of systems may be called Decision Support Systems or DSS. DSS systems may
take advantage of something we call an OLAP tool. OLAP stands for On-Line Analytical Processor. OLAP
tools are designed specifically to work with large volumes of archived data. The DSS and OLAP work on
top of a Data Warehouse database model. It is here that we will always see a Star schema take shape.
The Star schema is formed by the n-ary relationships between the fact tables and dimension tables in
the Warehouse model. A fact table surrounded by 3 dimension tables is participating in a Ternary
relationship with those tables. More than three tables form what we call an n-ary relationship. The
Star schema is utilized to provide better performance across large volumes of data within the Data
Warehouse.

figure 1: basic n-ary star schema

The Star Schema is not restricted to just data warehouses though. Ternary relationships can and do occur
within transactional data models. In most situations, the transactional database will not have n-ary
relationships beyond binary (m:n) relationships. When they do occur, it is easy to misinterpret them as
binary relationships and produce an awkward or incomplete data model.

We will present various examples as a four part series:

1. Presentation of a Ternary Transactional Model
2. Physical Modeling of a Ternary Transactional Database
3. Example of an N-ary (3+) Transactional Database Model
4. Physical Modeling of the N-ary Transactional Database