Simple Star Schema

• The Bold column name Indicates the primary key
• Lines indicate one to many foreign key relationships
• Bold italic column names indicate the primary key that is a foreign key to another table

Ads

Let’s point out a few things about the Star Schema above:

• Items listed in the boxes above are columns in the tables with the same names as the box names.
• The Primary key columns are in bold text.
• The foreign key columns are in italic text (you can see that the primary key from the green Dimension box is also a key in the orange box, the primary key from the turquoise box is also a foreign key in the orange box.)
• You can see that columns that are part of the primary key and the foreign keys are labeled in bold and italic text, like the key 1 in orange box.
• The foreign key relationships are identified by the lines that are used to connect the boxes that represent tables.

Even though a primary key value must be one of a kind in the rows of a dimension table the value can take place many times in a foreign key of a fact table, as in a many to one relationship. The many to one relationship can be present between the foreign keys of the fact table and the primary key they refer to in the dimension tables.

The star schema can hold many fact tables as well. Multiple fact tables are present because the have unrelated facts, like invoices and sales. With some situations multiple fact tables are present simply to support performance.

You can see multiple fact tables serving this purpose when they are used to support levels of summary data, more specifically when the amount is large, like with daily sales data.

Referencing tables are also used to define many-to-many relationships between dimensions. This is usually referred to as an associative table or even a cross-reference table. This can be seen at work in the sales database as well. In a sales database each product has one or more groups that is belongs to, each of those groups also contain many products.

The many-to-many relationships is designed through the establishment of a referencing table that is meant to define the various combinations of the products and groups within the organization.

We can also identify many-to-many relationships by having dimension tables with multicolumn primary keys that serve as foreign key references in fact tables.

A rough example of this would be yet again with the sale database, as we said before each product is in one or more groups and each of those grouse have multiple products, which is a many-to-many relationship.

Designing a Star Schema

When designing a schema for a database we must keep in mind that the design affects the way in which it can be used as well as the performance.

Due to this fact it is vital that one makes the preliminary investment in time and research they dedicate to the design a database one that is beneficial to the needs of its user. Let’s wrap things up with a few suggestions about things to consider when designing a schema:

Ads

• What is the function of the organization? Identify what the main processes are for the organization; it may be sales, product orders, or even product assembly, to name a few. This is a vital step; the processes must be identified in order to create a useful database.
• What is meant to be accomplished? As all databases, a schema should reflect the organization, in what it measures as well as what it tracks.
• Where is the data coming from? It is imperative to consider projected put in data and its sources will disclose whether the existing data can support the projected schema.
• What dimensions and attributes of the organization will be reflected by the dimension tables?
• Will there be dimensions that may change in time? If the organization contains dimensions that change often then it is better to measure it as a fact, rather then have it stored as a dimension.
• What is the level of detail of the facts? Each row should contain the same kind of data. Differing data would be addressed with a multiple fact table design or even by modifying the single table so that there is a flag to identify the differences can be stored with the data. You want to consider the amount of data, the space, and the performance needs when deciding how to deal with different levels of detail in data.
• If there are changes how will they be addressed, and how significant is historical information?  

First Page: Star Schema