A physical schema includes the following two primary components:
A physical schema consists of a set of tables, and those tables contain columns that store the actual data. The following topics describe each of these components in more detail.
In a data warehouse, the columns in tables store fact or attribute data.
The following are the three types of columns:
These columns store the IDs for attributes. IDs are often numeric and generally serve as the unique key that identifies each element of an attribute. All attributes have an ID column.
These columns store the text descriptions of attributes. Description columns are optional. However, most attributes have an ID column and at least one description column. If an attribute has additional description columns, you can create attribute forms for each column.
Note: When an attribute does not have a separate description column, the ID column serves as both the ID and description.
These columns store fact data. They are usually numeric.
In the LU_EMPLOYEE table, the Employee_ID column stores the unique IDs for each employee, while the other three columns store description information about each employee, including their names, e-mails, and addresses.
In the FACT_SALES table, the Dollar_Sales and Unit_Sales columns store values for these two facts. The remaining columns are attribute ID columns.
You can have several types of tables in the physical schema.
Lookup tables store information about attributes, including their IDs and any descriptions. They enable you to easily browse attribute data. A lookup table can store information for a single attribute or multiple related attributes
The above example would clearly explains about the way the lookup tables store the attribute data’s.
Relationship tables store information about the relationship between two or more attributes. They enable you to join data for related attributes. To map the relationship between two or more attributes, their respective ID columns must exist together in a relationship table.
Lookup tables can serve a dual function as both lookup and relationship tables in some circumstances. At other times, you need to have a relationship table that is distinct from any associated lookup tables.
Fact tables store fact data and attribute ID columns that describe the level at which the fact values are recorded. They enable you to analyze fact data with regard to the business dimensions or hierarchies those attributes represent.
There are two types of fact tables that you typically have in a data warehouse.
Base fact tables are tables that store a fact or set of facts at the lowest possible level of detail.
Aggregate fact tables are tables that store a fact or set of facts at a higher, or summarized, level of detail.
Base fact table will be having the data at the lowest granular level, where as the Aggregate fact table will be having the data at the level higher (ie) how here it gets aggregated and stored at month level.
Hope this helps for the day to know about the components involved in physical schema design!!
Have a good Day!!