Why Multi-valued Attributes Are a Problem

A multi-valued attribute occurs when a single column holds more than one value for the same entity instance (e.g., an Employee row with a Phone column containing “555-0101, 555-0199”). This violates First Normal Form (1NF)—the “one attribute → one value” rule—and must be resolved before implementation.

• Delimited lists: Commas, pipes, or semicolons inside a single column (e.g., Children = 'Dan,Mike,Tina').
• Arrays/JSON in a column: Storing sets as JSON/array values when each item is a separate fact.
• Repeating columns: Patterns like Phone1, Phone2, Phone3 (arbitrary limits, many NULLs).
• Order-dependent meaning: Assuming the first child name aligns with the first birth date.

1. Integrity & meaning: Values become ambiguous (which birth date matches which child?). You cannot enforce per-value constraints cleanly.
2. Performance: The DBMS must scan and parse strings; indexes are ineffective on delimited lists.
3. Scalability: Fixed limits (e.g., Phone1..Phone3) cap growth; variable lists complicate updates and reporting.
4. Anomalies: Redundancy, update inconsistencies, and difficulty preventing duplicates.

A full name like “Susan Anthony” is one value for a Name attribute—so it is not multi-valued. Many databases still split names into FirstName and LastName for usability (e.g., personalized salutations) and filtering; that’s a design choice, not a 1NF requirement.

Multi-valued attributes must be resolved before implementation:

• Add distinct attributes only when the set is small and fixed (e.g., HomePhone, WorkPhone), and each attribute has clear business meaning.
• Create a new entity for open-ended or per-value facts (e.g., children, skills, certifications). Model a 1:M (or M:N) relationship so each value is a separate row with its own constraints and indexes.

Details on both approaches follow in the next two lessons.

Multi-valued attributes break 1NF, blur meaning, and block integrity and performance optimizations. Treat each value as a separate row—either by adding a few well-defined attributes (fixed, small set) or, more commonly, by introducing a new entity for the repeating fact.

Next lesson: How to resolve a multi-valued attribute by adding attributes. After that, you’ll learn how to resolve it by creating a new entity.

[1] multi-valued attribute: An attribute that attempts to hold more than one data value for the same entity instance.