Data Modeling medium
The EAV pattern stores data as rows: `[EntityID, Attribute, Value]`. When is this acceptable?
- A When the collection of target attributes is vast, sparse, and highly dynamic (e.g., Medical Records) where defining hundreds of NULL columns would be inefficient.
- B When modeling standard user authentication profiles where the attributes are rigid, predictable, and shared by every single member of the application.
- C When implementing high-frequency financial ledgers that require strict ACID compliance and absolute mathematical consistency for every multi-row transaction.
- D When designing high-performance caching layers that prioritize O(1) read latency for simple key-based lookups over schema flexibility.
Answer: A. When the collection of target attributes is vast, sparse, and highly dynamic (e.g., Medical Records) where defining hundreds of NULL columns would be inefficient.
The Entity-Attribute-Value (EAV) pattern allows you to store an infinite number of attributes without altering the physical schema. However, this flexibility comes with a massive 'Query Tax': reconstructing a single entity into a usable record requires complex pivoting or multiple joins against the same table. While once common for product specs, modern systems often prefer JSONB columns in relational databases or dedicated document stores to gain flexibility without the EAV performance penalty.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?