Practise vocabulary for schema design patterns: star schema, snowflake, EAV, adjacency list, and closure table.
0 / 5 completed
1 / 5
A ___ schema organises data into a central fact table surrounded by dimension tables — the standard pattern for analytics/data warehousing.
A star schema has one central fact table (containing measurable events — sales, transactions) surrounded by denormalised dimension tables (Date, Product, Customer). Optimised for analytical query performance.
2 / 5
A ___ schema extends the star schema by normalising the dimension tables into multiple related tables, reducing redundancy at the cost of more JOINs.
A snowflake schema normalises dimension tables — for example, the Product dimension splits into Product → Category → Department. This reduces redundancy but requires more JOINs in analytical queries.
3 / 5
EAV (Entity-Attribute-Value) is a schema pattern used for ___ — storing entities with highly variable or unpredictable attribute sets.
EAV stores attributes as rows (entity_id, attribute_name, value) rather than columns, enabling flexible schemas. It's used in product catalogues with varying attributes, healthcare systems, and CMSs — but is hard to query efficiently.
4 / 5
An ___ list models a hierarchical tree structure by storing each node with a reference to its parent node in the same table.
The adjacency list pattern adds a parent_id column to the table. Simple to implement, but querying full subtrees requires recursive CTEs or multiple queries. PostgreSQL and MySQL support WITH RECURSIVE for this.
5 / 5
A ___ table explicitly stores all ancestor-descendant relationships in a hierarchy, enabling efficient subtree queries at the cost of storage space.
A closure table stores every (ancestor, descendant, depth) combination. This enables O(1) ancestor/descendant queries without recursion — queries are simple JOINs — at the cost of O(n²) storage for deep trees.