Resolve Multi-Valued Attributes by Creating a New Entity

A column should hold one value per row. When a fact can occur zero-to-many times for an entity (e.g., an employee’s children), storing lists like Children or BirthDates in the Employee table violates the single-value rule, blocks indexing, and makes queries/error-checking brittle.

• Unbounded counts: The number of values varies per instance (children, emails, certifications).
• Per-value attributes: Each value has its own details (a child’s name, birth date, relationship type).
• Frequent filtering/aggregation: You’ll query by those values (e.g., “children born before 1990”).

Do not add columns like Child1, Child2, …; they cap growth, invite nulls, and complicate logic.

Create a new entity that represents the repeating fact, and relate it 1:M to the parent.

-- Parent: one row per employee
CREATE TABLE Employees (
  EmpID      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  LastName   VARCHAR(80) NOT NULL,
  FirstName  VARCHAR(80) NOT NULL,
  Street     VARCHAR(120),
  City       VARCHAR(80),
  State      CHAR(2),
  Zip        VARCHAR(10)
);

-- Child entity: one row per dependent (child)
CREATE TABLE Dependents (
  DependentID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  EmpID       BIGINT NOT NULL,
  FullName    VARCHAR(120) NOT NULL,
  BirthDate   DATE NOT NULL,
  Relationship VARCHAR(32) DEFAULT 'Child',
  CONSTRAINT fk_dependents_employee
    FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)
    -- choose the action that matches your policy:
    -- ON DELETE RESTRICT | SET NULL | CASCADE
);

-- Example query: children born before 1990
SELECT e.EmpID, d.FullName, d.BirthDate
FROM   Employees e
JOIN   Dependents d ON d.EmpID = e.EmpID
WHERE  d.BirthDate < DATE '1990-01-01'
ORDER  BY d.BirthDate;

Why this works: each repeating value becomes its own row, enabling indexes, constraints, and precise filtering.

Anti-pattern (do not use)	Normalized design
Employees(EmpID, …, Children, BirthDates) Comma-separated lists; ambiguous pairing of child ↔ birth date; hard to query/validate.	Employees(EmpID, …) Dependents(DependentID, EmpID, FullName, BirthDate, Relationship) One row per dependent; clear FK; easy to sort/filter; integrity rules enforceable.

• Cardinality: Employee → Dependent is typically 1:M. If a dependent may relate to multiple employees (e.g., guardians), model M:N with a junction table EmployeeDependent.
• Constraints: Add CHECK (BirthDate <= CURRENT_DATE), and consider a unique rule like UNIQUE(EmpID, FullName, BirthDate) to avoid accidental duplicates.
• Delete policy: Pick an FK action (RESTRICT, SET NULL, or CASCADE) that matches HR retention rules.
• Indexes: Index Dependents(EmpID) and any frequent filter columns (e.g., BirthDate).

If you encounter a multi-valued attribute, that’s a strong signal you need another entity (or a junction entity). Model each value as its own row. You gain integrity, query speed, and unlimited capacity.

“Inventory” and “Medical history” are collections of many entity instances (e.g., items; appointments & treatments). Model the underlying entities; produce the collection via queries or views.

Before moving on to the next lesson, click the Exercise link below to test your ability to resolve multi-valued attributes.
MultiValued Attributes - Exercise
The next lesson lists entity and attribute constraints.