Choose One Attributes Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design
Choose-one And Finite Value Attributes

CREATE TABLE FACT (
. . .
highest_education number(2)
CONSTRAINT highest_education_check
CHECK (highest_education between 0 and 20);
size_of_immediate_family number(2)
CONSTRAINT family_check
CHECK (size_of_immediate_family between 0 and 20);
number_of_children number(2)
CONSTRAINT children_check
CHECK (number_of_children between 0 and 20);
own_or_rent char(1)
CONSTRAINT own_check
CHECK (own_or_rent in ‘U’,‘O’,’R’);
yearly_income_class number(1)
CONSTRAINT income_check
CHECK (yearly_income_class between 1 and 5);

Here, we see that a total of 8 bytes would be added to the fact table to provide fast reference to information about the type of customer who made the transaction. We also note that there are provisions for unknown values for the customer, as would be the case when a customer did not use their Grocery Club card, in this case, a “U” for unknown character values and a zero for unknown numeric values. Now, queries from end users desiring transaction information according to the type of customer are easy to respond to because the fact table does not need to be joined with the customer table. But, what about the product classifications? Because the product classifications are hierarchical in nature, we need to develop another mechanism for representing them in our fact table.

---

This is an excerpt from "High Performance Data Warehousing".

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.