Attributes-Entities   «Prev  Next»

Lesson 7Multi-valued Attributes
ObjectiveExplain the problem with multi-valued attributes and when they must be resolved.

Multivalued Attributes in DBMS: Examples and First Normal Form

A multivalued attribute is a property that can have several values for one entity instance. An employee may have several phone numbers, skills, or children. The modeling problem arises when independently meaningful facts are packed into one field or spread across arbitrary numbered columns instead of being represented with an appropriate structure.

For example, a Children field containing “Jack, Jenny, John” describes three people associated with one employee. If each child has a birth date, identity, and information that must be searched or updated separately, the design needs to preserve those facts together. A text list hides the structure that the application actually uses.

This lesson explains how to recognize the problem and decide when to resolve it. The following lessons develop two approaches: separate attributes for genuinely distinct roles, and related records for repeatable facts.

One Employee Record Can Describe Several Separate Facts

Employee 0985 has separate lists of child names and birth dates; after source verification, three child records keep each identifier, name, and date together.
Figure 5-7: Parallel lists hide the association between child names and birth dates. The lower table illustrates a possible mapping after checking the source; it does not establish that list position proves a match.

The original example includes employee 0985 with child names Jack, Jenny, and John and short date strings 1-1-00, 6-25-06, and 10-2-04. Those strings do not by themselves establish the century, date convention, or correct child/date pairing. A migration must resolve those questions from reliable source information.

For the teaching example below, assume a source review confirms the following mapping. The values are illustrative, not verified records of real people. EmployeeID remains text so that its leading zero is retained.

Illustrative child records after source verification
ChildIDEmployeeIDChildNameBirthDate
C010985Jack2000-01-01
C020985Jenny2006-06-25
C030985John2004-10-02

The repeated EmployeeID is intentional: it connects the three records to the same employee. Each ChildID identifies one child record. A child's name is descriptive information, not a dependable identifier, because different people can share a name.

How to Recognize a Multivalued Attribute

Ask whether a field contains one fact of the declared kind or a collection of independently meaningful facts. Punctuation is only a clue. The important evidence comes from requirements: must users search, validate, count, relate, or change the members separately?

  • Delimited lists: PhoneNumbers contains several numbers separated by commas or semicolons.
  • Parallel lists: ChildNames and BirthDates rely on matching positions to keep information associated.
  • Numbered columns: Skill1, Skill2, and Skill3 repeat the same kind of property with an arbitrary limit.
  • Parsing in everyday queries: Reports repeatedly split strings to recover the objects they need.
  • Per-member facts: Every certification needs its own issuer, date, status, or evidence.

Review examples at the boundaries. What happens when an employee has no listed children, one child, or more children than the form provides spaces for? What happens when two children share a birth date? These cases expose assumptions that a single convenient sample can hide.

The number of values currently stored is not the decisive test. A phone field may currently contain one number for every employee, yet the business permits several. Model the permitted situations rather than treating today's sample as a permanent limit.

How This Relates to First Normal Form

In the conventional introductory treatment of First Normal Form (1NF), a relation represents one value from the intended domain at each row-and-column intersection, without repeating groups. If the intended fact is one child name, a list of several child names substitutes a collection for that fact.

Declaring the collection as VARCHAR makes it a legal SQL string, but it does not make the hidden child facts individually represented in the relational design. Technical storage acceptance and a suitable business model are different questions.

Atomicity is interpreted with respect to the domain and the operations the model needs. It does not mean that a value can never be divided into smaller pieces. A date has year, month, and day components and can still be one date value. A text value can contain several words and still represent one name.

Numbered columns deserve scrutiny for the same design reason, although each individual cell may contain a scalar value. They encode repeated occurrences in the schema. Adding a fourth occurrence then requires another column instead of another record. Avoid relying on a slogan about commas to identify every modeling problem.

Why Parallel Lists Cause Problems

Pairing becomes fragile. If someone sorts the child names alphabetically without sorting the dates in the same way, the implied associations change. If one list has three entries and another has two, position alone cannot explain which date is missing. Keeping related values in one record makes their association explicit.

Validation moves into parsing code. A DATE column can apply date-type rules to one birth date. A free-text list first needs tokenization, whitespace handling, format interpretation, and error reporting for each member. Different applications may implement those steps differently.

Searching becomes more complicated. A substring search for Ann can also match Joanne or Annette. Delimiter-aware matching may improve that search, but escaping and inconsistent separators introduce more rules. A dedicated ChildName column allows an exact comparison against a single name value.

Updates require reconstruction. Correcting one child's birth date in a list may require reading and rewriting the larger value. Without appropriate concurrency control, two applications can overwrite each other's changes. Separate records provide clearer update targets, although transactions and concurrency controls are still necessary.

Counting and duplicate detection become uncertain. Counting commas assumes consistent formatting and that commas never appear inside values. Repeated names might represent duplicate input or different people. Assigning identifiers and defining row meaning gives these questions a more reliable basis.

Lists are not literally impossible to query or index. Specialized expressions, search features, and collection indexes may help. The practical concern is that a plain index on a packed string does not automatically provide the per-member semantics, keys, and constraints that a relational child structure makes explicit.

Composite Attributes Are a Different Issue

A composite attribute has meaningful components. An address might include street, city, postal code, and country. A multivalued attribute has multiple occurrences of a property, such as several addresses for one customer. An attribute can be both composite and multivalued.

A full name such as Susan Anthony can be one value in a FullName attribute. It does not violate a one-name-per-record rule because it contains a space. Similarly, a comma inside a legal organization name does not prove that the field contains several organizations.

Split a value into components when the requirements need those components independently and the decomposition has a defensible meaning. Do not assume that every person's name follows a universal first-name/last-name pattern. Decide how to store and display the actual information required by the application.

If a customer has several addresses and every address has its own city and postal code, each address occurrence needs its components kept together. Creating separate lists of streets, cities, and postal codes repeats the same fragile pairing problem shown in the child example.

Preview: Represent Each Child as a Related Record

The following self-contained PostgreSQL 18 example shows the structure behind the illustration. For simplicity, it assumes each child record is associated with exactly one employee in this application. That is a modeling assumption, not a claim about family relationships generally.

CREATE TABLE EmployeeExample (
    EmployeeID VARCHAR(10) PRIMARY KEY
);

CREATE TABLE ChildExample (
    ChildID VARCHAR(10) PRIMARY KEY,
    EmployeeID VARCHAR(10) NOT NULL,
    ChildName VARCHAR(200) NOT NULL,
    BirthDate DATE,
    FOREIGN KEY (EmployeeID)
        REFERENCES EmployeeExample (EmployeeID)
);

INSERT INTO EmployeeExample (EmployeeID)
VALUES ('0985');

INSERT INTO ChildExample
    (ChildID, EmployeeID, ChildName, BirthDate)
VALUES
    ('C01', '0985', 'Jack', DATE '2000-01-01'),
    ('C02', '0985', 'Jenny', DATE '2006-06-25'),
    ('C03', '0985', 'John', DATE '2004-10-02');

These statements illustrate the assumed verified mapping; they are not evidence of database execution. The identifiers C01 through C03 are teaching values. A production identifier allocation policy would need to cover the full population, not just this small example.

The foreign key requires a referenced employee record. The primary key distinguishes child records, and the DATE column provides a date representation. These rules do not prove that a supplied birth date is factually correct or that two different identifiers cannot accidentally represent the same person. Those are additional data-quality concerns.

SELECT ChildID, ChildName, BirthDate
FROM ChildExample
WHERE EmployeeID = '0985'
ORDER BY ChildID;

The expected result is the three illustrated child records. The query does not need to split strings or pair list positions. ORDER BY requests the display sequence; physical row order is not the relationship between the child and employee.

An unknown birth date can be NULL on the correct child record instead of causing a missing position in a separate list. The model still needs to define what “unknown” means and whether the application must distinguish it from withheld or unavailable information.

Choose Distinct Roles or Repeating Records

Separate columns can make sense when they describe different, stable roles. An order's RequestedDeliveryDate and ActualDeliveryDate are not merely Date1 and Date2; they express different facts. Likewise, a business may define one primary contact number and one emergency contact number with separate rules.

The test is meaning, not simply a small count. HomePhone and WorkPhone may appear sufficient until the application needs several work numbers, extensions, labels, or effective dates. If the occurrences share a common structure and can grow independently, a related contact table is usually a clearer fit.

Child1Name, Child2Name, and Child3Name impose a capacity limit without giving the positions a durable business meaning. Adding matching birth-date columns doubles the repeated structure. Another child requires a schema change, and a query must inspect several similar columns.

The next lesson considers resolving attributes through distinct attribute definitions. The following lesson develops a new entity for repeating facts. The choice should follow the requirements rather than the layout of the original paper form.

Some Collections Describe Many-to-Many Relationships

An employee's skill list often points to a shared vocabulary. SQL, accounting, or equipment maintenance can apply to many employees, and each employee can have several skills. In that case, an EmployeeSkill association connects Employee and Skill records.

Information such as proficiency or assessment date belongs to the association when it describes that employee's use of that skill. A uniqueness rule on the appropriate association key can prevent duplicate memberships while allowing the same skill to be linked to many employees.

Revisit the child example if two employees can be associated with the same child. A shared Child entity and an EmployeeChild association can represent that situation without duplicating the child's identity. Decide whether relationship type, eligibility period, or other details belong to the association.

Do not automatically create a separate entity for every token in a text field. First identify what the token represents: a value, an independently identified object, an occurrence, or a relationship. That decision determines the table's meaning and keys.

What About Arrays and JSON?

Modern DBMSs support intentional collection and document types. PostgreSQL has typed arrays and JSON types, including indexing support for jsonb. Their existence means that “collections cannot be queried or indexed” is too broad a claim.

A stored document, an external payload retained for audit, or a bounded collection processed as one unit may justify those types. The decision depends on access patterns, validation, ownership, and the required relationships. Valid JSON syntax alone does not establish correct business data.

For this lesson's independently maintained child facts, related rows make individual identifiers and ordinary relational constraints explicit. An array or JSON design would need its own strategy for those guarantees. Choose it deliberately rather than using it to avoid deciding what the data represents.

Resolve Existing Lists Without Inventing Facts

Keep the original source while designing a migration. Establish the separator and escaping rules, determine date formats and centuries, and check whether parallel lists are guaranteed to align. Record ambiguous cases for review instead of silently assigning dates by position.

Compare source and destination counts at the correct level. One employee record can become one employee plus three child records, so equal total row counts are not the expected result. Reconcile the number of employee identities and the number of confirmed child occurrences separately.

Validate references and identifier uniqueness, then review samples that include missing values, repeated names, and shared dates. Two children can have the same birth date, so that coincidence alone is not a reason to merge their records.

Keep a record of unresolved cases and the decisions used to resolve them. A technically successful import can still encode an incorrect assumption. The purpose of restructuring is to preserve meaning as well as improve how the data is stored.

When Should a Multivalued Attribute Be Resolved?

Resolve the structure when members need independent identity, validation, searching, relationships, or updates. Parallel lists and arbitrary numbered columns are strong signals that the model is hiding repeatable facts. A collection that is intentionally handled as a single documented value requires a separate, explicit design decision.

Before choosing a solution, answer three questions: What does one occurrence represent? Which facts belong together? Can the number of occurrences change? These questions connect attribute domains with the entity and relationship structure needed to store them.

For additional reading: Database Systems.

The definition of a multivalued attribute concerns several values for one entity instance; punctuation alone does not determine whether the attribute is multivalued.

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

SEMrush Software 7 SEMrush Banner 7