Practise vocabulary for schema design patterns: star schema, snowflake, EAV, adjacency list, and closure table.
0 / 14 completed
1 / 14
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 / 14
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 / 14
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 / 14
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 / 14
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.
6 / 14
PR Description
During code review, Sarah submitted a PR to update the user profile schema. She used a Snowflake `VARIANT` column to handle potentially missing or varying address fields for users. The comment from David read: 'This is a clever use of VARIANT, but I'm not sure if it fully addresses the long-term scalability concerns. We might need to revisit this approach as our user base grows and the complexity of these profiles increases.'
Which of the following best describes Sarah's schema design pattern choice in relation to David's concern?
Sarah's use of a VARIANT column is a pragmatic choice for handling evolving data within a Snowflake environment. However, David's comment highlights the potential risks associated with relying solely on a single, flexible data type – specifically regarding query performance and long-term scalability as the application grows. The correct answer acknowledges this balance, noting that while adaptable, VARIANT is an appropriate choice in the current context, aligning with agile development. Options A and B misinterpret Sarah's decision or present overly critical judgments without acknowledging the benefits of her approach; option C incorrectly frames the issue as a violation of normalization best practices when it's more about performance tradeoffs.
7 / 14
PR Description
During code review, Sarah submitted a PR to update the user profile schema. She used a Snowflake `VARIANT` column to handle potentially missing or varying address fields for users. The comment from David read: 'This is a clever use of VARIANT, but I'm not sure if it fully addresses the long-term scalability concerns. We might need to revisit this approach as our user base grows and the complexity of these profiles increases.'
Which of the following best describes Sarah's schema design pattern choice in relation to David's concern?
Sarah's use of a VARIANT column is a pragmatic choice for handling evolving data within a Snowflake environment. However, David's comment highlights the potential risks associated with relying solely on a single, flexible data type – specifically regarding query performance and long-term scalability as the application grows. The correct answer acknowledges this balance, noting that while adaptable, VARIANT is an appropriate choice in the current context, aligning with agile development. Options A and B misinterpret Sarah's decision or present overly critical judgments without acknowledging the benefits of her approach; option C incorrectly frames the issue as a violation of normalization best practices when it's more about performance tradeoffs.
8 / 14
PR Description
During code review, Sarah submitted a PR to update the user profile schema. She used a Snowflake `VARIANT` column to handle potentially missing or varying address fields for users. The comment from David read: 'This is a clever use of VARIANT, but I'm not sure if it fully addresses the long-term scalability concerns. We might need to revisit this approach as our user base grows and the complexity of these profiles increases.'
Which of the following best describes Sarah's schema design pattern choice in relation to David's concern?
Sarah's use of a VARIANT column is a pragmatic choice for handling evolving data within a Snowflake environment. However, David's comment highlights the potential risks associated with relying solely on a single, flexible data type – specifically regarding query performance and long-term scalability as the application grows. The correct answer acknowledges this balance, noting that while adaptable, VARIANT is an appropriate choice in the current context, aligning with agile development. Options A and B misinterpret Sarah's decision or present overly critical judgments without acknowledging the benefits of her approach; option C incorrectly frames the issue as a violation of normalization best practices when it's more about performance tradeoffs.
9 / 14
PR Description
During code review, Sarah submitted a PR to update the user profile schema. She used a Snowflake `VARIANT` column to handle potentially missing or varying address fields for users. The comment from David read: 'This is a clever use of VARIANT, but I'm not sure if it fully addresses the long-term scalability concerns. We might need to revisit this approach as our user base grows and the complexity of these profiles increases.'
Which of the following best describes Sarah's schema design pattern choice in relation to David's concern?
Sarah's use of a VARIANT column is a pragmatic choice for handling evolving data within a Snowflake environment. However, David's comment highlights the potential risks associated with relying solely on a single, flexible data type – specifically regarding query performance and long-term scalability as the application grows. The correct answer acknowledges this balance, noting that while adaptable, VARIANT is an appropriate choice in the current context, aligning with agile development. Options A and B misinterpret Sarah's decision or present overly critical judgments without acknowledging the benefits of her approach; option C incorrectly frames the issue as a violation of normalization best practices when it's more about performance tradeoffs.
10 / 14
David: 'I'm using a star schema for our customer data. It seems straightforward – a central `customers` fact table surrounded by dimension tables like `orders` and `products`. But I'm noticing some anomalies in the reporting, particularly when we need to analyze customer behavior across multiple product categories. Should I consider a different approach?',
The star schema is indeed a good starting point. However, the question highlights a potential issue: anomalies in reporting when dealing with complex relationships. A snowflake schema (extending dimensions) addresses this by normalizing data and reducing JOINs, which can significantly improve query performance. An EAV schema would be overkill for this scenario.
11 / 14
"Maria just posted in the #schema-design Slack channel: 'We're implementing a new user profile system. To accommodate varying address details – some users might have only city and state, others might provide full street addresses – I've chosen to use Snowflake VARIANT columns. It seems like the most adaptable solution.' What is Maria primarily attempting to achieve?
Maria is leveraging VARIANT columns to accommodate *varying* address details. This addresses the core challenge of handling unpredictable or missing attributes in a schema – it's about flexibility, not strict validation or normalization. While normalization could be considered later, the immediate need is adaptable storage.
12 / 14
"John (Lead Architect) asked during a standup: 'We're designing our product catalog schema. I want to ensure we can efficiently query for all products within a specific geographic region and category. What is the *most* efficient approach?'
A hierarchical tree structure (using nested tables) is the most efficient for representing and querying hierarchical data like product categories. This allows for optimized subtree queries – retrieving all products within a specific region and category becomes significantly faster compared to wide tables or EAV schemas.
13 / 14
"Ben (Junior Developer) is creating a new schema for tracking website user sessions. He wants to represent the relationships between users and their activities on different pages. Which schema pattern would best suit this scenario?
The scenario describes tracking relationships between users and their activities on different pages. A star schema with normalized dimension tables is ideal for this – it's designed to represent fact data (user sessions) surrounded by well-defined dimensions (users, pages, timestamps). EAV would be too flexible and complex; adjacency lists are not a standard schema pattern.
14 / 14
"Emily: 'We need to design a system for storing user preferences. Users can have any combination of preferred settings – from basic options like language and currency to highly specific ones like font size and color schemes.' What schema pattern is most appropriate?
The core of the problem is *unpredictable* attributes – users can have any combination. An EAV schema excels at this by separating entities (users), attributes (preference settings), and values (specific preferences). A star schema or snowflake schema would force rigid categorization and miss potential future preferences.
What does the "Schema Design Patterns Vocabulary" exercise practise?
Practise vocabulary for schema design patterns: star schema, snowflake, EAV, adjacency list, and closure table.
How many questions are in this exercise?
This exercise has 14 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database Schema Design category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Schema Design Patterns Vocabulary" part of a larger series?
Yes — it's one exercise in the Database Schema Design category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database Schema Design category page for related exercises, or browse the main Exercises hub for other IT English topics.