This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

At the core of data warehouse applications and OLAP (online analytical processing) is a specialized schema that relaxes the rules of Third Normal Form RDBMS schemas in favor of faster analysis and processing of large amounts of data. It's important to understand the difference between these kinds of schemas during application design or migration, rather than later when schema changes are more costly.

Most database developers encounter Third Normal Form schemas before any other schemas. The EMP/DEPT examples use Third Normal Form schemas as the basic pattern. In this form of database normalization, an object or entity is related to another object or entity by a primary key and foreign key on an individual basis. (The foreign key DEPTNO in the EMP table defines the relationship between EMP and DEPT tables.) Here is an example of a Third Normal Form database schema:

create table brands
(
    id number primary key,
    name varchar2(30) not null
);

create table products
(
    id number primary key,
    brand_id number not null,
    name varchar2(30) not null,
    foreign key (brand_id) references brands(id)
);

create table sales
(
    product_id number,
    when date,
    how_many number,
    foreign key (product_id) references products(id)
);

Normalization isn't always a good thing when dealing with large amounts of data. While it's ideal for data updates, inserts, deletes, and integrity, it can slow processing. To speed up processing, you can denormalize data into a star schema. A star schema consists of a set of object tables called dimensions, which stores attributes about a specific object, such as product, customer, or time. All of these tables join together through a single join table of facts. This kind of schema can be more natural to nontechnical end users who are more familiar with dealing with logical entities rather than entities and relationships. Here is the example from above, reimplemented as a star schema:

create table product_dims
(
    id number primary key,
    name varchar2(30) not null,
    brand varchar2(30) not null
);

create table time_dims
(
    id number primary key,
    when date
);

create table sale_facts
(
    product_id number not null,
    time_id number not null,
    how_many number,
    foreign key (product_id) references product_dims(id),
    foreign key (time_id) references time_dims(id)
);

Star schemas do have some inherent problems. For instance, the central fact table can grow very large, with an upper limit of the product of the number of rows in each dimension table. Also, the dimension tables are no longer normalized, so they are larger and harder to maintain with lots of duplicate data.

In my example above, brand was denormalized and needs to be maintained separately. To go back toward normalization a bit, there is also a snowflake schema, which is a star schema with some of the features of a Third Normal Form data. Here is the same oversimplified example, but in a snowflake schema:

create table brands2
(
    id number primary key,
    name varchar2(30) not null
);

create table product_dims2
(
    id number primary key,
    brand_id number not null,
    name varchar2(30) not null,
    foreign key (brand_id) references brands2(id)
);

create table time_dims2
(
    id number primary key,
    when date
);

create table sale_facts2
(
    product_id number not null,
    time_id number not null,
    how_many number,
    foreign key (product_id) references product_dims2(id),
    foreign key (time_id) references time_dims2(id)
);

The problem with snowflake schemas is that they may start to show signs of the performance problems of Third Normal Form queries. I don't recommend using snowflake schemas with Oracle's cost-based optimizer.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.