If generalization is the process of recognizing that diverse entities share a common identity, then common attributes are the evidence that proves it. Every valid generalization rests on a foundation of shared characteristics—attributes that appear in all (or most) subtypes with the same essential meaning.

But identifying common attributes is not as simple as matching column names. Real-world entities evolved independently, often in different departments, different systems, or different eras. What is 'customerName' in one system is 'clientFullName' in another and 'account_holder' in a third. Data types vary. Constraints differ. Optional in one place, required in another.

The skilled database designer must navigate this complexity, transforming a fragmented landscape of inconsistent attributes into a coherent, unified set of inherited properties. This is the art and science of common attribute analysis—the critical step that determines whether a generalization is meaningful and maintainable.

In this page, we'll master the techniques for identifying, reconciling, and properly placing common attributes in a generalization hierarchy.

Before we can identify common attributes, we must precisely define what 'common' means in the context of generalization. Superficial similarity is not enough—true commonality requires semantic equivalence.

Definition:

An attribute is common across a set of entity types E₁, E₂, ..., Eₙ if and only if:

1. It appears in all (or a significant majority of) the entity types
2. It serves the same semantic purpose in each entity type
3. It describes a property of the shared supertype concept, not a subtype-specific characteristic

The Three Dimensions of Commonality:

A rigorous attribute analysis follows a structured process. This ensures that all potential common attributes are identified and properly evaluated.

Real-world systems rarely use consistent naming. When generalizing entities from different sources, naming conflicts are inevitable. The database designer must systematically identify and resolve these conflicts.

Types of Naming Conflicts:

Type 1: Synonyms (Different Names, Same Concept)

Different terms refer to the same underlying attribute:

• 'clientId' / 'customerId' / 'accountHolderId' → all mean customer identifier
• 'effectiveDate' / 'startDate' / 'validFrom' → all mean when something becomes active
• 'amt' / 'amount' / 'value' → all mean monetary value

Resolution Strategy: Choose the most descriptive, standards-compliant name. Prefer full words over abbreviations. Document the renamed attributes and original names.

Type 2: Homonyms (Same Name, Different Concepts)

Identical terms refer to different underlying concepts:

• 'status' in ORDER (fulfillment state) vs. 'status' in USER (active/inactive)
• 'date' in INVOICE (invoice date) vs. 'date' in EMPLOYEE (hire date)
• 'type' in ACCOUNT (checking/savings) vs. 'type' in CUSTOMER (individual/corporate)

Resolution Strategy: These are not common attributes despite sharing names. Rename them with specific prefixes (orderStatus, userStatus) to clarify distinction.

Type 3: Format Variations (Same Concept, Different Formatting)

Same concept with different naming conventions:

• 'first_name' / 'firstName' / 'FirstName' / 'FIRST_NAME'
• 'date_of_birth' / 'dateOfBirth' / 'birthDate' / 'DOB'

Resolution Strategy: Standardize to your project's naming convention. Typically, choose snake_case or camelCase consistently and rename all instances.

When common attributes have different data types across subtypes, the database designer must harmonize them into a single type for the supertype. This requires careful analysis to avoid data loss or constraint violations.

Principles of Type Harmonization:

Principle 1: No Data Loss

The unified type must be able to store all valid values from all subtypes without truncation or loss of precision.

CUSTOMER.balance   DECIMAL(10,2)   // up to 99,999,999.99
SUPPLIER.balance   DECIMAL(8,2)    // up to 999,999.99
↓
PARTY.balance      DECIMAL(10,2)   // uses larger precision

Principle 2: Semantic Preservation

The unified type must preserve the meaning of the data. Widening a type is usually safe; narrowing is dangerous.

// Safe: widening
VARCHAR(50) + VARCHAR(100) → VARCHAR(100)

// Dangerous: narrowing
VARCHAR(100) → VARCHAR(50)  // potential truncation!

Principle 3: Type Compatibility

Some type combinations cannot be meaningfully unified:

// Incompatible: semantic mismatch
INTEGER (customer count) + DECIMAL(10,2) (account balance)
// These are different concepts, not type variations!

Attributes in different subtypes often have different constraints. When moving attributes to a supertype, these constraints must be reconciled—typically by taking the least restrictive constraint for the supertype, while preserving stricter constraints at the subtype level.

Nullability Constraints:

The most common constraint conflict is nullability:

CUSTOMER.tax_id    NOT NULL   (all customers must have tax ID)
SUPPLIER.tax_id    NOT NULL   (all suppliers must have tax ID)
EMPLOYEE.tax_id    NULL       (contractors may not have tax ID initially)

Resolution: The supertype must use NULL (the least restrictive). Stricter constraints are enforced at the subtype level:

-- Supertype definition
CREATE TABLE party (
    party_id VARCHAR(36) PRIMARY KEY,
    tax_id VARCHAR(20) NULL,  -- nullable in supertype
    ...
);

-- Subtype-level constraint for customers
ALTER TABLE customer 
    ADD CONSTRAINT customer_tax_id_required 
    CHECK (tax_id IS NOT NULL);

-- Subtype-level constraint for suppliers  
ALTER TABLE supplier
    ADD CONSTRAINT supplier_tax_id_required
    CHECK (tax_id IS NOT NULL);

-- Employee has no such constraint (nullable OK)

Unique Constraint Considerations:

Uniqueness across subtypes requires special attention:

Scenario 1: Attribute is unique within each subtype

• CUSTOMER.email is unique among customers
• SUPPLIER.email is unique among suppliers

Question: Should email be unique in the supertype PARTY?

Answer: It depends on business rules. If the same email can belong to a customer AND a supplier (same person in both roles), then email is NOT unique in PARTY. If email must be globally unique (one person = one party record), then email IS unique in PARTY.

Scenario 2: Attribute is unique in some subtypes only

• CUSTOMER.taxId is unique (each customer has unique tax ID)
• SUPPLIER.taxId is unique
• EMPLOYEE.taxId might have NULLs (not all have it)

Resolution: Use a partial unique index or application-level enforcement for non-null values.

Not all candidate common attributes appear in every subtype. Partial commonality—where an attribute appears in most but not all subtypes—requires nuanced handling.

Decision Framework for Partial Commonality:

Case 1: Present in All-Minus-One

Attribute appears in all subtypes except one:

CUSTOMER.phone: present
SUPPLIER.phone: present  
EMPLOYEE.phone: present
SYSTEM_USER.phone: absent (system accounts have no phone)

Decision: Move to supertype as nullable. The missing subtype simply has NULL values.

Case 2: Present in Majority

Attribute appears in most subtypes:

FULL_TIME.startDate: present
PART_TIME.startDate: present
CONTRACTOR.startDate: present
VOLUNTEER.startDate: absent (volunteers may come and go)
INTERN.startDate: absent (treated differently)

Decision: Consider whether the attribute describes the supertype concept (WORKER) or only certain subtypes. If it's essential to being a worker, move to supertype as nullable. If it's specific to formal employment, keep in applicable subtypes or create an intermediate type.

Case 3: Present in Minority

Attribute appears in few subtypes:

MANAGER.directReports: present (count of direct reports)
TECH_LEAD.directReports: present
ENGINEER.directReports: absent
DESIGNER.directReports: absent
ANALYST.directReports: absent

Decision: This attribute describes 'people who manage others', not all employees. Keep in subtypes, or introduce a PEOPLE_MANAGER intermediate supertype.

Once common attributes are placed in the supertype, they are inherited by all subtypes. Understanding inheritance semantics is crucial for correct schema implementation and query design.

Inheritance Principles:

Principle 1: Automatic Inclusion

Every instance of a subtype automatically has all supertype attributes. A MANAGER has all EMPLOYEE attributes plus manager-specific attributes.

EMPLOYEE: {empId, name, email, department, hireDate}
MANAGER: {empId, name, email, department, hireDate} + {budgetAuthority, teamSize}

Principle 2: Single Source of Truth

Inherited attributes exist in one place—the supertype. Subtypes do not duplicate these columns (unless using specific physical implementation strategies).

Principle 3: Uniform Access

Queries can access inherited attributes through the supertype:

-- This works for ALL employee types
SELECT empId, name, email FROM employee

-- This returns managers with inherited + specific attributes
SELECT e.empId, e.name, m.budgetAuthority 
FROM employee e JOIN manager m ON e.empId = m.empId

Principle 4: Constraint Inheritance

Subtypes inherit supertype constraints. A CHECK constraint on EMPLOYEE.hireDate applies to managers, engineers, and all other employee types.

We've comprehensively covered the analysis, reconciliation, and placement of common attributes in generalization. Let's consolidate the essential practices:

What's Next:

Now that we understand how to identify and place common attributes, we'll examine supertype creation in detail—how to define the supertype entity itself, establish its identity, structure its relationships, and integrate it into the broader schema.