Lesson 9	Resolve Multi-Valued Attributes
Objective	Describe how to resolve multi-valued attributes by creating a new entity.

How to Resolve Multivalued Attributes in a Relational Database

To resolve a multivalued attribute, move its individual values into a separate table and connect that table to the original entity with a foreign key. Give each new row an appropriate primary key. An employee with three children then has one employee row and three related child rows, rather than a list of names stored in one column.

This lesson develops that transformation from a conceptual modeling decision into a small relational schema. The important question is what each row represents. An employee row describes an employee; a child row describes one child. Keeping those facts separate makes their relationships explicit and lets the database enforce rules that would otherwise depend on parsing text.

Recognize a Multivalued Attribute

An attribute is multivalued when one entity instance can have several values for it. An employee can have multiple children, a customer can supply several telephone numbers, and a product can have several certifications. The number of values may differ between instances and can change over time. Review the earlier lesson on multivalued attributes if you need help distinguishing these from ordinary single-valued attributes.

In the original Employees table, employee 6141 has the names Dan, Mike, and Tina in a Children column. A separate BirthDates column contains three dates. The reader must assume that the first date belongs to the first name, the second date to the second name, and so on. The database sees two text values, not three securely paired name-and-date records.

That distinction matters when somebody reorders the names without reordering the dates, omits an unknown date, or inserts a comma into a name. Counting list elements may reveal some errors, but matching counts cannot prove that each date belongs to the right child. The structure should store those pairings directly.

Replacing the lists with Child1, Child2, and Child3 columns is also a repeating-group design. It imposes an arbitrary limit and requires more columns when an employee has another child. Queries must inspect each numbered column, and unused positions remain empty. A separate table represents the varying number of occurrences without changing the schema.

Before and After: Employees and Children

Before: employee rows contain lists of child names and birth dates. After: Employees links by EmpID to eight Children rows, each identified by ChildID. — The three employee rows remain in Employees. Eight individual Children rows replace the name and birth-date lists. ChildID identifies each child row; EmpID is the foreign key connecting it to an employee.

Follow employee 6141 through the diagram. Dan becomes ChildID 1 with BirthDate 1988-11-10; Mike becomes ChildID 2 with BirthDate 1992-12-07; Tina becomes ChildID 3 with BirthDate 1990-04-19. Each row carries EmpID 6141. The association no longer depends on the position of a value inside a string.

Employee 6142 has two related rows, Ginny and Mark. Employee 6143 has three, Jim, Rick, and Cindy. There are therefore three Employees rows and eight Children rows. Other employee attributes are omitted from the diagram for clarity; they stay in Employees rather than being repeated for every child.

Design question	Before	After
What represents a child?	A position in two text lists	One Children row
How is another child added?	Edit both lists carefully	Insert one related row
How is a birth date changed?	Find the correct list position	Update the row identified by ChildID
How is the employee identified?	Implicitly by the containing row	Explicitly by the EmpID foreign key

Build the New Entity in Four Steps

Define the entity. Children represents the individual children recorded for employees in this example. State that meaning before selecting columns. A child is more than a name: the application also needs a birth date and an employee association.
Move the dependent attributes together. Name and BirthDate belong on the same child row. Keep employee information, such as the employee's own name or department, in Employees. Do not copy employee details into every child record merely to avoid a join.
Choose the primary key. Use ChildID to identify each child row independently of its editable descriptive values. The example numbers 1 through 8 are identifiers, not ages, list positions, or a permanent ordering requirement.
Add the relationship. Put EmpID in Children and make it a foreign key referencing Employees.EmpID. This is the many side of the relationship, where several rows can refer to the same employee.

EmpID alone cannot be the Children primary key: employee 6141 occurs three times there. Conversely, EmpID is unique in Employees, where there is one row per employee. Repeating a foreign-key value on the many side is expected and does not recreate the original multivalued-column problem.

A child's name is not a reliable identifier because different children can share a name, and names can change. Even a name-and-birth-date combination is not guaranteed to distinguish every person. A generated identifier solves row identification, but it does not automatically detect duplicate real-world people. Duplicate detection still needs a business process and carefully chosen rules.

State the Cardinality and Participation Rules

For this teaching example, assume that each recorded child is associated with exactly one employee. An employee can have zero, one, or many recorded children. The diagram's 1-to-many label describes the overall relationship; the minimum participation on the child side for an employee is zero.

An employee without recorded children still has an Employees row. There is no placeholder Children row containing a blank name or a made-up birth date. Note that zero child rows means no children are recorded, which may differ from knowing that the employee has none. If that distinction matters, model the declaration or verification status explicitly.

The foreign key ensures that a recorded child refers to an existing employee. Adding NOT NULL to Children.EmpID makes that association mandatory for every child row. Neither constraint requires every employee to have a child. Requirements about minimum numbers of related rows need separate design and enforcement.

If two employees can be guardians of the same child, change the model instead of duplicating the child. Keep Children keyed by ChildID, remove the single employee association from that table, and introduce EmployeeChild with EmpID and ChildID as foreign keys. A composite primary key on those two columns prevents duplicate employee-child associations. Relationship-specific attributes, such as guardian role, belong in that bridge table.

How This Relates to First Normal Form

In the conventional relational design taught here, first normal form removes repeating groups and treats each attribute value as a single value in its defined domain. If the application needs to identify, validate, search, or relate individual children, a comma-separated list is a poor substitute for individual rows. The new table makes those individual facts available to relational operations.

Atomicity depends on the intended use of a value. A person's displayed name can be one value for this example, while another application may need separate name components. The test is not whether a string contains spaces or punctuation; it is whether independently meaningful occurrences are being packed into one field.

Moving repeating values into rows does not prove that an entire schema satisfies every higher normal form. You must also examine functional dependencies and other business rules. For example, if the Children table unnecessarily repeats an employee's department, that introduces a separate dependency problem. Continue normalization after resolving the repeating group.

Modern databases also support arrays, JSON, and specialized indexes. Those features can suit document storage or other deliberate designs; it is inaccurate to say that all collection values are unqueryable or unindexable. Here, child-level identity, dates, relationships, and constraints make a related table the straightforward design.

SQL Example: Create the Related Tables

The following illustrative SQL uses PostgreSQL 18 syntax. The modeling principles apply across relational systems, but identity columns, date literals, and referential actions should be checked for the selected DBMS. Run a teaching example only in a suitable practice database.

CREATE TABLE Employees (
    EmpID INTEGER PRIMARY KEY
);

CREATE TABLE Children (
    ChildID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    EmpID INTEGER NOT NULL,
    Name VARCHAR(100) NOT NULL,
    BirthDate DATE,
    CONSTRAINT fk_children_employee
        FOREIGN KEY (EmpID) REFERENCES Employees (EmpID)
        ON DELETE RESTRICT
);

CREATE INDEX ix_children_empid ON Children (EmpID);

INSERT INTO Employees (EmpID) VALUES (6141), (6142), (6143);

INSERT INTO Children (EmpID, Name, BirthDate) VALUES
    (6141, 'Dan',   DATE '1988-11-10'),
    (6141, 'Mike',  DATE '1992-12-07'),
    (6141, 'Tina',  DATE '1990-04-19'),
    (6142, 'Ginny', DATE '1989-01-11'),
    (6142, 'Mark',  DATE '1994-11-07'),
    (6143, 'Jim',   DATE '1978-10-19'),
    (6143, 'Rick',  DATE '1983-02-27'),
    (6143, 'Cindy', DATE '1986-06-21');

Employees is intentionally minimal to match the diagram. A production table would contain the other employee attributes required by the application. ChildID is generated when omitted from an insert. Its exact values are not a business rule, and applications should not assume that generated identifiers are consecutive or reusable.

BirthDate allows NULL here so an unknown date can remain unknown. If the business requires a verified birth date before a child can be registered, change that rule and use NOT NULL after validating existing data. Never substitute a convenient fictional date merely to satisfy a constraint.

ON DELETE RESTRICT prevents an employee from being deleted while child rows still reference that employee. This is an explicit teaching policy, not a universal retention policy. CASCADE would delete those related rows automatically; SET NULL would require a nullable EmpID and a different rule about whether children may exist without an employee association.

The EmpID index supports common employee-to-child lookups. In PostgreSQL, a foreign-key declaration does not automatically create an index on the referencing columns. Additional indexes should follow actual query needs and measured costs. Normalization improves the representation and enforcement of facts; it does not guarantee that every query becomes faster.

Query Individual Children Without Parsing Lists

Once dates have their own typed column, a date condition applies directly to each child. This query returns children born before 1990 and identifies their associated employee:

SELECT e.EmpID, c.ChildID, c.Name, c.BirthDate
FROM Employees AS e
JOIN Children AS c ON c.EmpID = e.EmpID
WHERE c.BirthDate < DATE '1990-01-01'
ORDER BY e.EmpID, c.BirthDate, c.ChildID;

For the eight supplied records, the expected names are Dan, Ginny, Jim, Rick, and Cindy. Mike, Tina, and Mark do not satisfy the date condition. A row with an unknown BirthDate would not satisfy it either. These are expected results from the sample data, not a captured database execution.

To count recorded children while retaining employees who have none, use a left join and count the child key:

SELECT e.EmpID, COUNT(c.ChildID) AS ChildCount
FROM Employees AS e
LEFT JOIN Children AS c ON c.EmpID = e.EmpID
GROUP BY e.EmpID
ORDER BY e.EmpID;

The sample counts are 3, 2, and 3. COUNT(c.ChildID) returns zero for an employee with no matching child; COUNT(*) would count the outer-join placeholder row. This is a practical reason to understand both the relationship and the meaning of the aggregate expression.

Convert Existing Lists Without Losing Associations

A design diagram is not a complete migration procedure. Before changing a populated database, retain a recoverable copy and define how existing writes will be handled during the conversion. Work in a staging area where questionable records can be reviewed before they enter the constrained tables.

Inventory the source values. Identify separators, blank entries, escaped punctuation, duplicate names, and differing date formats. Determine whether list order really preserves the intended pairing.
Pair and validate each occurrence. Match each child with the correct date and employee. Quarantine mismatched or ambiguous lists for review rather than guessing. Convert valid dates into a date type using their known source format.
Load parents, then children. Establish the employee keys first, insert validated child rows, and retain a migration mapping where needed to trace new identifiers back to the source.
Reconcile the result. Compare per-employee counts and individual name/date associations, not just a grand total. For this diagram, verify all eight children, including each of employee 6141's three date pairings.
Switch application access deliberately. Update forms, reports, imports, and queries. Retire the old list columns only after consumers use the new model and rollback requirements have been satisfied.

Validation should also cover business rules that are not expressed in the example schema. For instance, future birth dates might need rejection during data entry. Do not present CHECK (BirthDate <= CURRENT_DATE) as a portable solution: DBMS restrictions and assumptions about time-dependent expressions vary. Choose an implementation supported by the actual platform.

Apply the Pattern to Other Collections

For customer telephone numbers, the related table might record the number, its type, and whether it is preferred. For product certifications, a shared Certification entity and a ProductCertification bridge may be appropriate because the same certification can apply to many products. Decide whether a repeated value is a dependent occurrence or an independently shared entity.

Terms such as inventory and medical history name collections, but they do not automatically determine the tables. An inventory design may need Product, Location, and StockBalance; a medical-history design may need Encounter and Diagnosis. Some collections also have their own identity and attributes, such as a named inventory count. Model the required facts rather than treating every collection name as either always an entity or never an entity.

Check Your Understanding

An employee has four children. How many rows represent that employee and the children? One Employees row and four Children rows. Which key repeats? EmpID repeats as a foreign key in Children; ChildID remains unique. What changes when a fifth child is recorded? Insert another child row without adding a column.

The reusable rule is to identify the repeating fact, give each occurrence a suitable row and key, and connect it to its owner or related entities. Confirm cardinality, optional participation, and validation rules before implementing the tables. The next lesson on entity and attribute constraints develops those rules further.

References

Multivalued Attributes: Exercise

Practice identifying the new entity and its relationship in the Multivalued Attributes Exercise.