In a data warehouse star schema, how do foreign key columns typically appear in a fact table compared with dimension tables?

Introduction / Context:
Data warehouse design often uses star schemas, consisting of a central fact table surrounded by dimension tables. Understanding how primary keys and foreign keys are organised in this schema is critical for designing queries, ensuring referential integrity, and optimising performance. This question focuses on where foreign key columns usually appear in fact and dimension tables.

Given Data / Assumptions:

• A star schema has one large fact table and multiple smaller dimension tables.
• Dimension tables hold descriptive attributes, while the fact table holds numeric measures.
• Each dimension table has a primary key, often a surrogate key.
• The fact table relates to dimensions using foreign key columns.

Concept / Approach:
In a star schema, the fact table is the central table that records business events or transactions. For each dimension involved (such as time, product, customer, location), the fact table stores a foreign key that points to the corresponding dimension's primary key. Dimension tables are usually not designed with foreign keys back to the fact table; instead, the relationship is navigated from the fact table to dimensions. This design simplifies queries and supports efficient joins for analytical workloads.

Step-by-Step Solution:
1. Identify the core purpose of the fact table: storing measures like sales amount, quantity, and counts.2. For each measure record, the fact table needs to know which product, customer, date, and other dimensions it relates to, so it stores foreign keys.3. Dimension tables contain detailed attributes (names, categories, hierarchies) keyed by their primary key, often a surrogate key like Product_Key or Customer_Key.4. Option A states that the fact table contains foreign key columns referencing the primary keys of each dimension table, and dimension tables usually do not contain foreign keys back to the fact table. This aligns with standard star schema design.5. Option B reverses the relationship, which is not typical in star schemas.6. Option C is wrong because foreign keys are central to relating facts and dimensions, even if some models do not enforce them physically.7. Option D suggests that fact and dimension tables share the same primary key, which would collapse the star into one table and is not how star schemas are structured.8. Therefore, Option A is correct.

Verification / Alternative check:
Standard data warehousing books and Kimball-style design methodologies describe star schemas with fact tables that have multiple foreign key columns such as Date_Key, Product_Key, Store_Key, and Customer_Key. These keys reference dimension tables that hold descriptive attributes. Example SQL queries show joins from fact to dimensions using these foreign key relationships, further confirming that the fact table is the place where foreign keys are stored.

Why Other Options Are Wrong:
Option B is wrong because dimension tables do not typically reference specific fact tables; doing so would create many-to-many entanglements and reduce modularity.Option C is wrong because even if foreign keys are not always enforced by the database engine, the logical design assumes that the fact table has foreign keys to dimensions.Option D is wrong because sharing the same primary key across all tables would not reflect the many-to-one relationships normally present between facts and dimensions.

Common Pitfalls:
Some designers new to data warehousing try to place foreign keys in dimension tables, confusing the direction of relationships. Another pitfall is failing to create surrogate keys in dimension tables, relying instead on natural keys that may change over time, which complicates history tracking. Remembering that the fact table points outward to dimensions via foreign keys helps keep the star schema clean and efficient for analytics.

Final Answer:
The fact table contains foreign key columns that reference the primary keys of each related dimension table, while dimension tables usually do not contain foreign keys back to the fact table.