Data Modeling medium
In Rails/Django, a 'Polymorphic Association' allows a `Comment` to belong to either a `Post` or a `Video`. Why is this often considered an anti-pattern in SQL?
- A It consumes excessive amounts of physical memory during complex traversal operations that require multiple recursive self-joins.
- B It breaks Referential Integrity at the database level, as you cannot define a standard Foreign Key constraint that validates against multiple tables.
- C It is not natively supported by modern high-level ORMs like ActiveRecord or Django, forcing developers to write raw, unoptimized SQL for all associations.
- D It requires the use of specialized semi-structured JSONB columns that are significantly slower to index and query than standard relational attributes.
Answer: B. It breaks Referential Integrity at the database level, as you cannot define a standard Foreign Key constraint that validates against multiple tables.
Polymorphic associations (e.g., a 'Comments' table that points to either 'Posts' or 'Videos' via a 'type' column) bypass the database's built-in referential integrity. Because a Foreign Key must target exactly one table, the database cannot verify that the `commentable_id` actually exists in the target table defined by `commentable_type`. This shifts the burden of data consistency entirely to the application layer, which is prone to bugs and 'orphaned' records.
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)?