Throughout this module, we've developed the individual skills of conceptual design—understanding high-level modeling, constructing ER diagrams, identifying entities, and discovering relationships. Now it's time to bring these skills together to create an initial schema: a complete, coherent conceptual model ready for validation and refinement.

The initial schema is not a final artifact frozen in time. It's the first complete draft—good enough to review with stakeholders, detailed enough to identify gaps, and structured enough to transition into logical design. Creating this schema requires synthesis: combining entities, relationships, attributes, and constraints into a unified whole.

This page guides you through the assembly process, provides validation techniques, discusses documentation practices, and prepares you for the next phase of database design.

An initial schema is the first complete version of your conceptual data model. It integrates all the pieces you've developed:

• All identified entities with their attributes
• All discovered relationships with cardinality and participation
• Key constraints (primary keys, unique constraints)
• Known business rules and domain constraints
• Any specializations or advanced constructs (EER elements)

Initial ≠ Final

The word "initial" is deliberate. This schema will evolve:

• Stakeholder review will reveal misunderstandings
• Normalization during logical design may split entities
• Physical design considerations may suggest changes
• Requirements themselves may change

The goal isn't perfection—it's a solid foundation for iteration.

What Makes a Schema 'Complete Enough'

An initial schema should be:

1. Comprehensive: Covers all identified requirements; no known gaps
2. Consistent: No contradictory elements or naming conflicts
3. Documentable: Decisions can be explained and justified
4. Reviewable: Clear enough for stakeholders to understand and critique
5. Evolvable: Structured to accommodate likely changes

It doesn't need to be:

• Implementation-ready (that's logical/physical design)
• Performance-optimized (that's premature at this stage)
• Tooling-specific (notation should be transferable)

Schema assembly brings together all your conceptual design work. Follow this systematic process:

Step 1: Consolidate Entity Inventory

Create a master list of all entities:

1. Collect all entity candidates from your discovery work
2. Eliminate duplicates (same concept, different names)
3. Resolve naming inconsistencies (choose canonical names)
4. Classify entities: strong, weak, or associative (junction)
5. List each entity with its primary key attribute(s)

Result: A definitive list like:

• Customer (CustomerID)
• Order (OrderNumber)
• Product (ProductSKU)
• OrderLine (OrderNumber + LineNumber) — weak entity

Step 2: Complete Attribute Specification

For each entity, list all attributes:

1. Gather attributes from discovery notes
2. Identify key attributes (underline in diagram)
3. Mark composite attributes with their components
4. Mark multivalued attributes
5. Mark derived attributes
6. Note any domain constraints (e.g., Status must be one of: Active, Inactive, Pending)

At this stage, you may note general types (text, number, date) but formal data types wait for logical design.

Step 3: Map All Relationships

Consolidate your relationship discoveries:

1. List every relationship with its participating entities
2. Specify cardinality from both sides
3. Mark participation (total or partial) for each entity
4. Document relationship attributes if any
5. Note any constraints on relationships

Result should look like:

• Customer (partial) —places(0,N)→ Order (total): Customer may have zero or more orders; each Order must have exactly one Customer
• Order —contains— OrderLine: identifying relationship (Order is owner, OrderLine is weak)

Step 4: Draw the Complete Diagram

With all components identified, create the full ER diagram:

1. Place entities on the diagram with meaningful spatial organization
2. Add relationships connecting entities
3. Annotate cardinality and participation
4. Add attribute ovals (or list inside entities for compact notation)
5. Mark special constructs (weak entities, specializations)
6. Ensure no components are orphaned

Step 5: Add Constraint Documentation

Capture constraints that don't fit diagram notation:

• Business rules: "An order can only be shipped after payment is confirmed"
• Derived calculations: "OrderTotal = SUM of (LineQuantity × LinePrice)"
• Cross-entity constraints: "Manager must be an employee of the same department"
• Temporal constraints: "EndDate must be >= StartDate"

These go in supplementary documentation alongside the diagram.

Before presenting the schema for review, perform systematic validation to catch issues you can identify yourself.

Validation 1: Requirements Traceability

Every requirement should map to the schema:

1. List all data requirements from your requirements document
2. For each requirement, identify which entity/attribute/relationship supports it
3. Flag requirements with no clear schema support → model gaps
4. Flag schema elements with no clear requirement → potential over-engineering

Example traceability:

Validation 2: Query Walkthrough

For key queries stakeholders need, verify the model can answer them:

1. List expected queries (from interviews, reports, dashboards)
2. For each query, trace a path through the model
3. Verify needed attributes exist along the path
4. Ensure cardinalities allow the expected results

Example:

• Query: "List all orders for a customer with product details"
• Path: Customer → Order → OrderLine → Product
• Verification: Path exists with appropriate cardinalities ✓

Validation 3: Instance Testing

Create sample instances to verify the model handles real data:

1. Invent a realistic scenario with multiple entity instances
2. Draw or list instance values
3. Verify relationships can be established as expected
4. Check cardinality constraints hold
5. Ensure no valid scenarios are blocked

Example:

• Customer C1 places Orders O1, O2
• Order O1 contains Products P1, P2
• Order O2 contains Product P1, P3
• Does the model allow this? (Should be yes for typical order system)

Validation 4: Edge Case Analysis

Consider edge cases and boundary conditions:

• New customer with no orders yet → model allows (partial participation)?
• Order with zero line items → should model allow? (probably no—total participation)
• Employee with no manager (CEO) → model allows?
• Product in no orders → allowed (partial participation)?

Edge cases often reveal participation constraint issues.

Validation 5: Semantic Accuracy

Verify the model captures meaning, not just structure:

• Can the model distinguish valid from invalid states?
• Are there states the model allows that shouldn't be possible?
• Are there valid states the model cannot represent?

Example: If an order can only have one shipping address, but the model shows M:N between Order and Address, invalid states are possible.

The initial schema must be validated by stakeholders who understand the domain. They'll catch issues you can't—because you don't know the business as deeply as they do.

Preparing for Review

1. Create presentation materials: Large, clear diagrams. Avoid dense, technical drawings. Consider separate views for different audiences.

2. Write a glossary: Define every entity in business terms. "Customer: An individual or organization who has registered to make purchases."

3. Prepare walkthrough scenarios: Real examples stakeholders can follow. "Let's trace what happens when customer Jane Doe places an order..."

4. List assumptions and decisions: Document where you made judgment calls. "We assumed employees can only belong to one department. Is this correct?"

5. Identify open questions: What couldn't you determine independently? "Does a product need to be categorized before it can be sold?"

Running the Review

Effective review sessions:

• Start with context: Remind stakeholders of the purpose. "This model represents the data for the order management system."

• Walk through, don't lecture: Step through scenarios, asking for confirmation. "So a customer places multiple orders over time—is that right?"

• Encourage questions: Silence isn't agreement. Probe: "Does anything look unexpected or missing?"

• Focus on semantics, not notation: Non-technical stakeholders may struggle with ER notation. Translate: "This line means one customer can have many orders."

Handling Feedback

Review feedback falls into categories:

Corrections: The model is wrong.

• "Actually, customers can have multiple shipping addresses."
• Action: Fix the model.

Additions: Something is missing.

• "We also need to track promotional codes on orders."
• Action: Add to model if in scope; clarify scope if not.

Clarifications: The model is unclear.

• "I don't understand what 'OrderLine' means."
• Action: Improve naming or documentation.

Scope Questions: Uncertainty about whether something belongs.

• "Should we include supplier information too?"
• Action: Discuss with project stakeholders to confirm scope.

Future Requirements: Things that will be needed but aren't in current scope.

• "Eventually, we'll need subscription handling."
• Action: Note for future; don't add to current scope unless directed.

Iterating After Review

Review typically requires model updates:

1. Incorporate corrections immediately
2. Add confirmed in-scope additions
3. Update documentation for clarifications
4. Re-validate affected areas
5. Schedule follow-up review if changes are substantial

Most conceptual designs go through 2-3 review iterations before stabilizing.

A diagram alone doesn't constitute complete documentation. The initial schema should be documented thoroughly for future reference, team communication, and design continuity.

Essential Documentation Components

1. Entity Catalog

For each entity, document:

• Name: Canonical entity name
• Description: What this entity represents in business terms
• Primary Key: Identifying attribute(s)
• Attributes: Full list with descriptions
• Constraints: Any entity-level rules
• Notes: Decisions, assumptions, gotchas

Example:

Entity: Customer
Description: An individual or organization registered to make purchases
Primary Key: CustomerID
Attributes:
  - CustomerID: Unique identifier for the customer
  - Name: Full name of individual or organization name
  - Email: Primary contact email (unique)
  - RegistrationDate: Date customer account created
  - Status: Active, Inactive, or Suspended
Constraints:
  - Email must be unique across customers
  - Status must be one of the defined values
Notes: We treat individuals and organizations as the same entity type.
       Consider splitting if different attributes are needed.

2. Relationship Catalog

For each relationship, document:

• Name: Relationship name
• Entities: Participating entities with roles
• Cardinality: From each entity's perspective
• Participation: Mandatory or optional for each entity
• Attributes: Any relationship attributes
• Description: What this relationship means

3. Glossary

Define all terms used in the model:

• Entity names
• Attribute names
• Relationship names
• Domain-specific terminology

A glossary ensures everyone interprets terms consistently. When a new team member asks "What's an SKU?", the glossary answers.

4. Assumptions and Decisions Log

Record the reasoning behind decisions:

Date: 2024-01-15
Decision: Customer and Organization are the same entity
Rationale: Current requirements don't distinguish them;
           attributes are identical; simplifies model.
Revisit if: Business needs different handling for B2B vs B2C.
           Consider generalization/specialization.

This log prevents revisiting settled issues and explains why the model looks the way it does.

5. Known Limitations

Document what the model doesn't do:

• "This model tracks current state only; historical employee-department assignments are not tracked."
• "Product variants (sizes, colors) are not modeled; each variant is a separate Product."
• "Multi-currency pricing is out of scope."

Clear limitations prevent incorrect expectations.

Documentation Practices

• Keep it current: Outdated documentation is worse than none—it misleads.
• Store with the design: Documentation should be versioned alongside the model.
• Use accessible formats: Wikis, markdown files, or collaborative docs that team members can access.
• Link to source: Reference requirements documents, interview notes, and review meeting notes.

How do you know if your initial schema is good? Beyond basic correctness, quality schemas exhibit several characteristics that distinguish professional work from amateur attempts.

Criterion 1: Semantic Richness

The model captures meaning, not just structure:

• Relationship names convey domain semantics ("purchases" vs. generic "has")
• Cardinalities reflect actual business rules, not defaults
• Constraints capture domain restrictions
• The model distinguishes valid from invalid real-world states

A semantically rich model can be read as a description of the domain. Someone unfamiliar with the business should understand it from the model.

Criterion 2: Minimal Redundancy

Each fact is represented once:

• No attribute appears in multiple entities unless it's a foreign key
• No relationship path is duplicated
• Derived data is marked as derived, not stored

Redundancy in conceptual models leads to redundancy in databases, causing update anomalies.

Criterion 3: Appropriate Abstraction

The model operates at the right level:

• High enough to be technology-independent
• Low enough to capture essential detail
• No implementation artifacts (foreign key columns, index hints)
• No application artifacts (screen fields, button actions)

Criterion 4: Consistency

The model is internally coherent:

• Naming conventions are uniform (all singular, all PascalCase, etc.)
• No contradictory constraints (A requires B, but B requires A)
• Notation is consistent throughout
• Similar concepts are modeled similarly

Criterion 5: Evolvability

The model can grow:

• Adding new entities doesn't require restructuring existing ones
• Common extension points are designed in (e.g., status codes as entities allow adding new statuses)
• Over-specialized structures are avoided where generalization may be needed

Criterion 6: Visual Clarity

The diagram communicates effectively:

• Related entities are grouped spatially
• Relationship lines don't cross unnecessarily
• Notation is legible at review-presentation size
• Colors or groupings (if used) convey information

Self-Assessment

Before presenting for review, rate your schema against these criteria:

• Below 70 points: More work needed
• 70-85 points: Ready for review with known issues
• 85+ points: High confidence, ready for formal review

The initial schema will be transformed into a logical schema—typically a relational schema ready for implementation. Preparing for this transition ensures smooth continuity.

What Logical Design Will Do

Logical design transforms the conceptual model:

• Entities → Tables: Each entity becomes one or more tables
• Attributes → Columns: With specific data types
• Relationships → Foreign Keys/Junction Tables: Implementing cardinality
• Multivalued Attributes → Separate Tables: Normalization
• Composite Attributes → Columns or Decomposition: Design choice
• Specializations → Table Strategies: Single table, multiple tables, or combined

Information to Provide for Logical Design

To facilitate transition, ensure your conceptual design provides:

1. Clear Primary Keys: Every entity needs a clear identifier. Natural keys are preferred at conceptual level; surrogates may be added during logical design.

2. Cardinality and Participation: These determine foreign key placement and NULL constraints.

3. Weak Entity Indicators: Weak entities become tables with composite keys.

4. Relationship Attributes: These move to junction tables for M:N relationships.

5. Specialization Constraints: Disjoint/overlapping and total/partial guide table strategy.

6. Domain Constraints: Valid values for attributes guide CHECK constraints and lookup tables.

Anticipating Normalization

Logical design includes normalization—ensuring the design avoids update anomalies. While normalization is a logical design activity, you can anticipate issues:

• Repeated groups of attributes: May indicate entity missing (normalize to separate table)
• Transitive dependencies: Attribute depends on non-key attribute (may need decomposition)
• Composite attributes stored as single value: May complicate querying

A well-constructed conceptual model rarely needs significant restructuring during normalization—these issues are usually avoided by good entity identification.

Handoff Checklist

Before transitioning to logical design, verify:

• All entities have primary keys identified
• All relationships have cardinality specified
• All relationships have participation constraints
• Weak entities are marked with identifying relationships
• Multivalued attributes are identified
• Composite attributes have components listed
• Specializations have constraints (disjoint/overlap, total/partial)
• Business rules requiring CHECK constraints are documented
• Any open questions are documented for resolution during logical design

Transition Meeting

If different people do conceptual and logical design, hold a handoff meeting:

• Walk through the complete model
• Explain assumptions and decisions
• Highlight any areas of uncertainty
• Identify open issues requiring resolution
• Agree on documentation access and version control

We've completed the conceptual design journey—from understanding high-level modeling principles through creating a validated initial schema. Let's consolidate the full module learning:

The Conceptual Design Discipline

Conceptual design is both an art and a discipline. The art lies in understanding domains, recognizing patterns, and making judgment calls when information is ambiguous. The discipline lies in systematic techniques, thorough validation, and rigorous documentation.

The best database designers combine both—creative insight guided by methodical process. They produce models that are accurate, complete, and elegant; models that stakeholders recognize as capturing their domain; models that serve as solid foundations for years of system evolution.

What's Next in the Database Design Journey

With conceptual design complete, the next phase is logical design—transforming the conceptual model into a schema for a specific data model paradigm (typically relational). Logical design includes:

• Mapping ER elements to relational tables
• Applying normalization to eliminate redundancy
• Specifying integrity constraints
• Refining the design for specific database capabilities

The conceptual model you've created makes logical design faster and more accurate. Instead of guessing at table structures, the logical designer transforms a validated, documented conceptual design systematically.

Congratulations on Completing This Module

You now possess comprehensive skills in conceptual database design. You can analyze domains, construct ER diagrams, identify entities and relationships, create initial schemas, and validate your work to professional standards. These skills form the foundation of all database design work.