What is the Star Schema?

The Star Schema is basically the simplest form of a data warehouse. This schema is made up of fact tables and dimension table. We have covered dimension tables in previous articles but the concept of fact tables is fairly new.

A fact table contains measurable or factual data about an organization. The information contained in the schema is usually numerical, additive measurements, the tables can consist of numerous columns and an extensive amounts of rows.

The two tables are different from each other only in the way that they are used in the schema. They are actually made up of the same structure and the same SQL syntax is used to create them as well.

Interestingly enough in some schemas a fact table can also play the role of a dimension table in certain conditions and vice versa. Though they may be physically a like it is vital that we also understand the differences between fact table and dimension tables.

A fact table in a sales database, used with the star schema, could deal with the revenue for products of an organization from each customer in each market over a period of time. However, a dimension table in the same database would define the organizations customers, the markets, the products, and the time periods that are found in the fact tables.

When a schema is designed right it will offer dimensions tables that enable the user to leaf through the database and get comfortable with the information that it contains. This helps the user when they need to write queries with constraints so that the information that gratifies those constraints is routed back into the database.

Star Schema Important Issues

As with any other schema performance is a big deal with the Star Schema. The decision support system is particularly important; users utilize this system to query large quantities of data. Star Schema’s happen to perform the most adequate decision support applications.

Another issue that is important mention are the roles that fact and dimension tables play in a schema. When considering the material databases, the fact table is essentially a referencing table, where as the dimension table plays the role of a referenced table.

We can correctly come to the conclusion that a fact table has a foreign key to reference other tables and a dimension table is the foreign key reference from one or multiple tables.

Tables that are references or are referenced by other tables have what is known as a primary key. A primary key is a column or columns with contents that specifically identify the rows. With simple star schemas, the fact table’s primary key can have multiple foreign keys.

The foreign key can be a column or a group of columns in a table which has values that are identified by the primary key of another table. When a database is developed the statements used to make the tables should select the columns that are meant to form the primary keys as well as the foreign keys. Below is an example of a Star Schema.

Next Page: Simple Star Schema