Mapping Composite, Multivalued, and Derived Attributes

Learning outcomes

Conceptual models can include composite attributes, multivalued attributes, and derived attributes. Relational schemas must represent each carefully. A poor mapping creates repeating columns, comma-separated values, duplicated calculations, or ambiguous fields. A good mapping preserves atomic facts and one clear row grain.

Composite attributes

A composite attribute contains meaningful subparts. A postal address might conceptually contain street, city, region, postal code, and country.

A relational mapping can flatten the components:

streetcityregionpostal_codecountry_code

This is appropriate when the address belongs to one parent, has no independent identity, and its components need separate querying or validation.

Do not flatten blindly

If a Customer can have billing, shipping, site, and historical addresses, repeatedly adding columns becomes brittle:

billing_streetbilling_citybilling_postal_codeshipping_streetshipping_cityshipping_postal_code

A separate CustomerAddress entity may be more appropriate.

When a composite attribute becomes an entity

Address deserves separate entity treatment when it has:

• independent identity;
• multiple parent uses;
• verification/geocoding state;
• effective dates;
• delivery metadata;
• relationships to zones or facilities.

The conceptual decision can change as requirements grow.

Multivalued attributes

A conceptual Customer may have many phone numbers. Do not map this as:

phone1phone2phone3

and do not map it as:

phones = '+49..., +1..., +98...'

Both approaches make the number of values awkward to constrain and query.

Map multivalued attributes to a child relation

CREATE TABLE customer_phone (    customer_phone_id INTEGER PRIMARY KEY,    customer_id       INTEGER NOT NULL,    phone_number      TEXT NOT NULL,    phone_type        TEXT,    is_primary        INTEGER NOT NULL DEFAULT 0,    FOREIGN KEY (customer_id)      REFERENCES customer(customer_id));

One phone number becomes one row.

Choose the child row grain

One CustomerPhone row might represent one normalized telephone endpoint. If verification, validity period, extension, or contact preference matters, those facts belong to that row or to related child records.

Multivalued simple value versus full entity

A tag list may begin as a simple multivalued attribute. If tags are shared and governed, model Tag plus an associative entity:

Tag(  tag_id,  tag_name)AssetTag(  asset_id,  tag_id)

This supports uniqueness, shared reuse, descriptions, aliases, and taxonomy.

Derived attributes

A derived attribute can be calculated from other facts. Examples:

• age from date_of_birth;
• duration from start/end timestamps;
• line_total from quantity × unit price;
• current_balance from ledger entries;
• is_overdue from due date and current time.

Default rule: store sources, derive results

Prefer storing stable source facts and deriving results when cheap and deterministic.

opened_atclosed_at-- derive:duration = closed_at - opened_at

Storing both timestamps and duration creates redundant data unless a deliberate performance or auditing need exists.

Historical derived values can become facts

Not every value that looks calculable should always be recomputed. Suppose invoice total depends on tax rules and rounding that existed at transaction time. The final charged amount may need to be stored as a historical fact even if it was originally calculated.

Ask whether the value must reproduce the historical decision or can always be derived from current data and rules.

Cached/materialized derived data

For performance, systems sometimes store a derived value:

work_order.total_parts_cost

even though it can be derived from PartUsage. This is denormalization. Once stored, the design needs an ownership rule:

• Who updates it?
• Is it updated in the same transaction?
• Can it be rebuilt?
• How is drift detected?

Chapter 13 covers denormalization and derived data in depth.

Composite values with units

A measurement is often composite:

weight_valueweight_unit

Storing weight = 25 without unit semantics is ambiguous. You can standardize to one canonical unit or preserve value + unit with a reference domain.

Money is not “just numeric”

Money generally requires amount plus currency:

charged_unit_price NUMERICcurrency_code      CHAR(3)

If all rows are guaranteed to use one currency by database/business scope, currency may be implicit at a higher level. Otherwise, storing the amount alone is semantically incomplete.

Structured JSON is not a universal escape hatch

JSON can be appropriate for semi-structured data, but moving every multivalued or composite concept into JSON can sacrifice ordinary foreign keys, unique constraints, and query clarity. Use JSON when variability is a real requirement, not merely to avoid relational modeling.

WorkshopHub examples

Complete relational fragment

CREATE TABLE customer_phone (    customer_phone_id INTEGER PRIMARY KEY,    customer_id       INTEGER NOT NULL,    phone_number      TEXT NOT NULL,    phone_type        TEXT,    is_primary        INTEGER NOT NULL DEFAULT 0,    FOREIGN KEY (customer_id)      REFERENCES customer(customer_id),    UNIQUE (customer_id, phone_number));CREATE TABLE tag (    tag_id    INTEGER PRIMARY KEY,    tag_name  TEXT NOT NULL UNIQUE);CREATE TABLE part_tag (    part_id INTEGER NOT NULL,    tag_id  INTEGER NOT NULL,    PRIMARY KEY (part_id, tag_id),    FOREIGN KEY (part_id) REFERENCES part(part_id),    FOREIGN KEY (tag_id) REFERENCES tag(tag_id));

Chapter 5 checkpoint

Summary and next chapter

Chapter 5 completed the translation from conceptual/logical models into relational structures. You can now map entities, 1:N and 1:1 relationships, M:N associations, inheritance, composite attributes, multivalued attributes, and derived values. Chapter 6 shifts from mapping to integrity: domains, entity integrity, referential integrity, checks, defaults, and business rules that span rows, tables, and time.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• C. J. Date, Database Design and Relational Theory.
• Thomas Connolly and Carolyn Begg, Database Systems.