Entities and Entity Sets

Names matter. In database design, the names you choose for entities, attributes, and relationships determine whether your model is a clear communication tool or an opaque puzzle that only its creator can decipher.

Consider two approaches to the same model:

Poor naming:

tbl_c(c_id, nm, addr1, addr2, dt, stat)
tbl_o(o_id, c_fk, dt1, dt2, amt, st)

Clear naming:

Customer(customer_id, full_name, billing_address, shipping_address, registration_date, status)
Order(order_id, customer_id, order_date, ship_date, total_amount, order_status)

Both models contain the same information, yet one is immediately understandable while the other requires documentation lookup for every column. In a model with hundreds of entities and thousands of attributes, poor naming creates significant ongoing costs—in development time, bug rates, onboarding effort, and maintenance burden.

This page establishes comprehensive principles and practices for naming database elements. These aren't arbitrary style preferences; they're battle-tested conventions that improve clarity, reduce errors, and make databases more maintainable over their multi-year lifespans.

Database names serve multiple stakeholders over long timeframes. A table named in 2023 might still be queried daily in 2033, by developers who never met the original designer. Names are the primary documentation embedded in the schema itself.

Stakeholders Who Read Database Names:

1. Database Developers — Writing queries, creating views, building stored procedures
2. Application Developers — Mapping tables to objects, writing data access code
3. Data Analysts — Creating reports, performing ad-hoc analysis
4. Business Users — Viewing data dictionaries, understanding available data
5. DBAs — Tuning performance, maintaining indexes, managing backups
6. Future Maintainers — Understanding legacy systems, making modifications
7. AI/ML Engineers — Understanding data semantics for model features

Each stakeholder benefits from clear, consistent naming that reveals meaning without requiring external documentation.

The Costs of Poor Naming:

Immediate Costs:

• Longer development time as developers look up abbreviation meanings
• More bugs from confusion between similarly-named elements
• Increased onboarding time for new team members

Long-Term Costs:

• Accumulated technical debt as inconsistencies multiply
• Difficulty evolving the schema without breaking things
• Knowledge loss when original developers leave

Entity names (which become table names) follow specific conventions that enhance clarity and consistency.

1. Singular vs. Plural:

The most debated naming decision. Both have valid arguments:

Singular (Recommended):

• Customer, Order, Product, Employee
• Represents the entity type ("What IS this thing?")
• Reads naturally: "Each Customer has an ID"
• More consistent with ER modeling theory
• Avoids irregular plurals (Person→People, Status→Statuses?)

Plural:

• Customers, Orders, Products, Employees
• Represents the collection ("What does the table CONTAIN?")
• Some teams prefer this for SQL clarity: SELECT * FROM Customers

Choose one and be consistent. Most data modeling standards recommend singular.

2. Case Convention:

PascalCase:

• CustomerOrder, ProductCategory, OrderLineItem
• Natural for multi-word names
• Common in conceptual models and some RDBMS

snake_case:

• customer_order, product_category, order_line_item
• Standard in PostgreSQL, MySQL, common in SQL
• Highly readable; clear word boundaries

UPPERCASE:

• CUSTOMER_ORDER, PRODUCT_CATEGORY
• Traditional in Oracle and older systems
• Less common in modern applications

3. Meaningful and Specific Names:

Avoid generic names that could mean anything:

# Bad - too generic
Item, Record, Entity, Object, Data, Info

# Good - specific
Product, TransactionRecord, CustomerEntity, DataExportLog

4. Domain Terminology:

Use terms from the business domain:

• In healthcare: Patient, Physician, Diagnosis (not User, Provider, Problem)
• In finance: Account, Ledger, Transaction (not Record, Entry, Item)
• In education: Student, Course, Enrollment (not User, Class, Signup)

5. Avoiding Reserved Words:

Don't name entities with SQL reserved words:

# Problematic names (reserved words)
Order, User, Table, Index, Date, Key, Select, Group

# Solutions
SalesOrder, SystemUser, LookupTable, SearchIndex, EventDate, ApiKey, SelectOption, ProductGroup

Prefixing or making more specific avoids reservation conflicts.

Attribute names (column names) require careful attention because they appear in every query, every report, and every line of data access code.

1. Descriptive and Complete:

Attributes should fully describe what they contain:

-- Bad
amt, dt, nm, addr, stat

-- Good
order_total_amount, order_placed_date, customer_full_name, shipping_address, order_status

2. Case Convention (Consistent with Entities):

Use the same case convention as entity names:

-- snake_case (recommended for SQL)
customer_id, first_name, last_name, email_address, registration_date

-- camelCase (common in some ORMs)
customerId, firstName, lastName, emailAddress, registrationDate

3. Primary Key Naming:

Several approaches exist:

[entity]_id:

customer_id, order_id, product_id

• Most common and recommended
• Clear which entity the key identifies
• Self-documenting in JOINs

id alone:

Customer.id, Order.id, Product.id

• Simpler, requires table qualification
• Common in some ORM patterns

pk_[entity]:

pk_customer, pk_order

• Explicit about being primary key
• Less common; prefix adds noise

4. Foreign Key Naming:

Standard approach: Match the referenced table's primary key name:

Order.customer_id → Customer.customer_id
OrderItem.product_id → Product.product_id

With role clarification: When multiple FKs reference the same table:

Flight.departure_airport_id → Airport.airport_id
Flight.arrival_airport_id → Airport.airport_id

Employee.manager_employee_id → Employee.employee_id
Employee.mentor_employee_id → Employee.employee_id

5. Boolean Attribute Naming:

Boolean attributes should read naturally:

-- Good: reads as question with yes/no answer
is_active, is_verified, has_subscription, can_login, allows_notifications

-- Poor: unclear intention
active, verified, subscription, login, notifications

6. Date and Time Naming:

Include type and context:

-- Include what kind of date/time
created_at             -- timestamp of creation
updated_at             -- timestamp of last update
order_placed_date      -- date order was placed
subscription_start_date
contract_end_date
delivery_estimated_at  -- timestamp with time component

Relationships connect entities, and their names should describe the connection meaningfully.

1. Verb-Based Relationship Names:

Relationships represent actions or associations. Name them with verbs:

Customer --places--> Order
Employee --works_in--> Department
Student --enrolls_in--> Course
Doctor --treats--> Patient
Product --belongs_to--> Category

The relationship name should read naturally in a sentence:

• "A Customer places an Order"
• "An Employee works in a Department"

2. Directional Reading:

Relationships are typically read in one direction:

Customer (1) --places--> (N) Order
"One customer places many orders"

Order (N) <--placed_by-- (1) Customer
"Many orders are placed by one customer"

You may label both directions or choose one based on which is more natural.

3. Junction Table Naming:

For many-to-many relationships, junction tables need clear names:

Option A — Combined entity names:

StudentCourse (or student_course)
UserRole (or user_role)
ProductCategory (or product_category)

Option B — Semantic name (preferred when applicable):

Enrollment (student-course relationship with attributes like grade)
EmployeeSkill (employee-skill with proficiency level)
OrderItem (order-product with quantity and price)

Semantic names make more sense when the junction has its own attributes—it's essentially a reified relationship.

4. Role Names in Self-Referential Relationships:

When an entity relates to itself, role names clarify participation:

Employee (as manager) --manages--> Employee (as subordinate)
Person (as parent) --parent_of--> Person (as child)
Component (as assembly) --contains--> Component (as part)

In implementation:

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_employee_id INT REFERENCES Employee(employee_id),
    mentor_employee_id INT REFERENCES Employee(employee_id)
);

5. Constraint Naming:

Name constraints explicitly for easier maintenance:

-- Primary Keys
CONSTRAINT pk_customer PRIMARY KEY (customer_id)
CONSTRAINT pk_order PRIMARY KEY (order_id)

-- Foreign Keys
CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)

-- Unique Constraints
CONSTRAINT uq_customer_email UNIQUE (email)

-- Check Constraints
CONSTRAINT chk_order_total_positive CHECK (total_amount >= 0)

Naming conventions:

• pk_[table] for primary keys
• fk_[table]_[referenced] for foreign keys
• uq_[table]_[column(s)] for unique constraints
• chk_[table]_[rule] for check constraints

Naming conventions may differ across model levels, though consistency within each level is essential.

Conceptual Level (ER Model):

Focused on business understanding:

• Natural language names
• Full words, no abbreviations
• Domain terminology precisely
• Spaces allowed: 'Customer Order', 'Product Category'
• Case may be flexible: 'Customer Order' or 'CustomerOrder'

Example:

Entities: Customer, Product, Order, Order Line Item
Attributes: Full Name, Email Address, Order Date, Unit Price
Relationships: places, contains, belongs to

Logical Level (Relational Schema):

Preparing for implementation:

• Remove spaces: use PascalCase or snake_case
• Still semantic, but format-compliant
• May add key conventions

Example:

Tables: Customer, Product, Order, OrderLineItem (or order_line_item)
Columns: full_name, email_address, order_date, unit_price, customer_id
Constraints: pk_customer, fk_order_customer

Physical Level (Database Implementation):

Platform-specific considerations:

• May adapt to RDBMS conventions (Oracle UPPERCASE, PostgreSQL lowercase)
• May add physical prefixes if required by standards
• Index names, tablespace names, etc.

Example (Oracle):

Tables: CUSTOMER, PRODUCT, SALES_ORDER
Columns: FULL_NAME, EMAIL_ADDRESS, ORDER_ID
Indexes: IDX_CUSTOMER_EMAIL, IDX_ORDER_DATE

Abbreviations are a major source of naming confusion. Minimize them, and when used, apply them consistently.

The Case Against Abbreviations:

• 'Cust' — Customer? Custom? Custody? Custodian?
• 'Num' — Number? Numeric? Numerical?
• 'Addr' — Address? Addressee? Addressing?
• 'Org' — Organization? Organic? Original?

Abbreviations save typing but cost comprehension. Modern SQL editors autocomplete names, making abbreviation savings minimal while comprehension costs are ongoing.

When Abbreviations Are Acceptable:

1. Universally understood abbreviations:

   • id (identifier)
   • qty (quantity)
   • amt (amount) — though 'amount' is clearer
   • URL, SSN, ISBN — standard acronyms

2. Domain-standard terms:

   • In chemistry: H2O, pH not 'hydrogen_ratio'
   • In finance: ROI, APR, not 'return_on_investment'
   • In technology: API, SQL, HTTP

3. When length constraints require:

   • Some older databases limit identifier lengths (30 characters in Oracle)
   • Abbreviate thoughtfully, document in data dictionary

Organizations benefit from formal naming standards—documented conventions that all database work must follow.

Components of a Naming Standard:

1. Case Rules:

• "All table names use PascalCase"
• "All column names use snake_case"
• "All constraint names use lowercase with underscores"

2. Word Order Rules:

• "Primary keys are [table]_id"
• "Foreign keys are [role]_[table]_id"
• "Boolean columns start with is_, has_, or can_"

3. Length Limits:

• "Table names maximum 30 characters"
• "Column names maximum 30 characters"
• "Constraint names maximum 30 characters"

4. Reserved Word Handling:

• "Never use SQL reserved words as names"
• "If business term conflicts, suffix with type: user_account, order_record"

5. Abbreviation Policy:

• "Full words preferred; abbreviate only if exceeding length limit"
• "Only approved abbreviations from registry"

6. Prefix/Suffix Rules:

• "No tbl_, col_, fk_ prefixes on logical names"
• "Physical index names start with idx_"
• "Audit columns: created_at, updated_at, deleted_at"

Sample Naming Standard Document:

=== Database Naming Standard v1.0 ===

TABLE NAMES:
- Singular nouns: Customer, Order, Product
- PascalCase: CustomerOrder, ProductCategory
- No prefixes: NOT tbl_Customer
- Maximum 30 characters

COLUMN NAMES:
- snake_case: customer_id, first_name
- Primary key: [table]_id
- Foreign key: [role]_[table]_id if needed
- Boolean: is_*, has_*, can_*
- Dates: *_date for date only, *_at for timestamp
- Maximum 30 characters

CONSTRAINT NAMES:
- Primary key: pk_[table]
- Foreign key: fk_[table]_[referenced_table]
- Unique: uq_[table]_[column(s)]
- Check: chk_[table]_[description]
- Index: idx_[table]_[column(s)]

ABBREVIATIONS:
- Prefer full words
- Allowed: id, qty, amt, sku, url
- Refer to abbreviation registry for others

Enforcement:

A naming standard is only valuable if enforced:

• Code reviews check naming compliance
• Automated linters validate naming patterns
• Data catalog tools flag violations
• New team members trained on standards
• Exceptions require documented approval

We've explored naming conventions comprehensively—from first principles of why naming matters to practical standards you can adopt. Good names are not mere aesthetics; they are essential infrastructure for understandable, maintainable databases.

What's next:

With this module on Entities and Entity Sets complete, you now have a thorough understanding of entities—the fundamental building blocks of ER modeling. You understand what entities are, how they're grouped, typed, instantiated, and named. This foundation prepares you for the next modules, which explore attributes, relationships, and more advanced ER concepts.