Creating a Conceptual Data Model

Creating a conceptual (or high-level) data model is the first crucial step in database design. It focuses on identifying the main entities, their attributes, and the relationships between them, without delving into technical details. This conceptual model serves as a communication tool between stakeholders and developers.

---

1: Business Requirements and Scope

Before designing anything, you need to thoroughly understand what the system is supposed to do and what data it needs to manage.

1. Gather Information:
   • Interview Stakeholders: Talk to users, business analysts, domain experts, and decision-makers. Ask open-ended questions about their processes, data needs, and pain points.
   • Review Existing Documentation: Look at reports, forms, business rules, and any current system documentation.
   • Observe Operations: If possible, observe the actual business processes to see how data is used.
2. Define the Scope:
   • Clearly articulate what the data model will cover and what it won't. This helps manage expectations and prevents scope creep.
3. Identify Key Business Processes:
   • Understand the main activities that generate or consume data. For example, "Placing an Order," "Registering a Customer," "Managing Inventory."

Visualising the model will be a great help for the next steps. See Appendix A

2: Identify Entities

Entities are the fundamental "things" or objects about which you want to store information. They typically correspond to nouns in your business requirements.

1. Brainstorm Potential Entities:
   • Read through your gathered requirements and highlight all significant nouns. Examples: Customer, Product, Order, Employee, Department.
2. Filter and Refine Entities:
   • Is it distinct? Does each instance of this entity need to be uniquely identifiable?
   • Does it have attributes? Does it have properties you want to store (e.g., a Customer has a Name, Address, etc.)?
   • Is it relevant to the scope? Is it necessary for the system's purpose?
   • Avoid Redundancy: Combine similar entities if they represent the same concept.
   • Examples of what might NOT be an entity: A simple attribute (e.g., "Color" might be an attribute of "Product" rather than its own entity, unless "Color" has its own complex properties like "Hex Code" and "Pantone ID").

3: Identify Attributes for Each Entity

Attributes are the characteristics or properties that describe an entity.

1. List Relevant Properties:
   • For each identified entity, list all the pieces of information you need to store about it.
   • Example for Customer entity: Customer ID, First Name, Last Name, Email Address, Phone Number, Shipping Address, Billing Address.
   • Example for Product entity: Product ID, Name, Description, Price, Stock Quantity, Category.
2. Determine Primary Keys:
   • For each entity, identify a unique identifier called the primary key. This attribute (or set of attributes) uniquely identifies each instance of the entity.
   • It should be unique, non-null, and stable (not likely to change). Often, an artificial ID (e.g., CustomerID, OrderID) is created if no natural unique identifier exists or is suitable.
3. Refine Attributes:
   • Granularity: Break down composite attributes (e.g., Full Name into First Name, Last Name).
   • Data Types (Conceptual): At this high level, you might just note the type of data (e.g., text, number, date, boolean) without specifying database-specific types.
   • Nullability: Consider if an attribute can be optional (nullable) or if it's always required.

4: Identify Relationships Between Entities

Relationships describe how entities are associated with each other.

1. Find Associations:
   • For every pair of entities, ask: "Do these two entities interact or relate in any way?"
   • Example: A Customer places an Order. An Order contains Products.
2. Determine Relationship Type (Cardinality):
   • This defines how many instances of one entity can relate to how many instances of another entity.
   • Refer to Cardinalities for more information.
3. Assign Relationship Names (Optional but Recommended):
   • Use verbs to describe the relationship from both directions (e.g., Customer places Order; Order is placed by Customer).

5: Review and Refine the Model

This is an iterative process. The first draft is rarely the final one.

1. Validate with Stakeholders:
   • Present the conceptual data model to the business users and stakeholders.
   • Explain the entities, attributes, and relationships in plain business language.
   • Ask questions: "Does this accurately reflect your business processes?" "Are we missing any critical information?"
2. Check for Completeness:
   • Does the model capture all necessary data for the identified business requirements?
3. Check for Consistency and Clarity:
   • Are naming conventions consistent? Is the diagram easy to understand?
4. Identify Ambiguities and Missing Information:
   • Are there any relationships that are unclear? Any attributes that need further definition?
5. Iterate:
   • Based on feedback, make necessary adjustments and repeat the review process until all stakeholders agree that the model accurately represents the business domain.

Appendices

A: Visualize with Termboard

Drawing diagrams helps in understanding and communicating the model. For the next steps you can use a visual way to create your model. Termboard provides you with all tools to visualize your conceptual model.

Open Conceptual Model Template

• Open File > Load Examples > Conceptual Data Model Template .
• It uses:
  • Rectangles for Entities.
  • Ovals connected to rectangles for Attributes.
  • Arrow lines connecting entities for Relationships.
  • Blue borders for Primary Keys

Draw the Diagram

Place entities on canvas

Placing elements (terms) on the canvas is explained in the Create term quickstart. You can provide the description of the entity in the Term sidebar

Using the provided template as mentioned above, you will get a few extra fields in the term sidebar. Choose the TAB DETAILS to see the extra fields.

• Element type: Specify if it is an 'Entity' (shows as rectangle) or an 'Attribute' (shows as elipse)
• Primary key: Specify 'Yes' if it is a primary key (shows as blue border)
• Data type: Optionally Specify the data type of the Attribute or Entity

Add Attributes to Entities

Adding Attributes works the same as adding Enitities. Select for Element type 'Attribute'

Draw relations

Adding relations is explained in the Create relation Quickstart. Similar to the entities and attributes you can also provide more information of the relation in the sidebar. The extra fields are also available, but are not applicable for a relation, so you can keep them empty.

Adding cardinalities to the relations is explained here: Relation Details

Save and or export

• First give your model a good Document name File > Document properties .
• You can quickly save it in the browser (works only local on your pc) File > Save
• You can save it for others to work on it File > Save File > JSON
• You can also save it as image or complete PDF document File > Export > *