Data modeling is the practice of representing data structures and their relationships through conceptual modeling. The benefits of implementing this process during the design phase of your database project include improved data integrity, a more efficient development cycle, a clearer vision of the entities that are being represented in your database, and a powerful visual aid for collaboration and database design.
Methodologies and the Entity-Relationship Model
You may be familiar with the term Entity-Relationship Model (ERM). ER is technically a methodology for data modeling. A methodology is just a defined set of processes and methods which are followed during the design and development of a software project. Other methodologies of data modeling exist, such as Object Model and Oracle CASE. ER is the most common and simplest method of modeling that can be used almost universally for application design.
Proposed in 1976 by Peter P. Chen, the Entity-Relationship model provides us with one of the most common and useful components of a data modeling methodology – the Entity-Relationship diagram. Simply put, ER diagrams are visual diagrams that show database information represented as entities and relationships. If you’re familiar with the Relationships tool found in Microsoft Access, you already know what an ER diagram is.
ER diagrams do a great job of representing relational data models because entities and relationships between those entities map well to primary data components in your data model and their relationships between one another. For example, your company many need a small database to store data about customers, orders, products, and representatives. Each of these four primary entities is directly related to a table in the database.
Looks great, but we still need to represent our relationships between these entities so we can define our indexes and foreign keys. Relationships have a plethora of meta data that can be associated with them, such as direction, existence, type, cardinality, connectivity, and direction. We will focus on the connectivity and cardinality of the relationships, which define the mapping between entities in our diagram. There exist three primary relationships:
One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)
A one-to-one relationship describes two entities with a direct mapping, such as “Each employee has one parking spot, and each parking spot is assigned to one employee”.
A one-to-many relationship describes one entity related to more than one entity, such as “Each employee will have one assigned break period, but more than one employee can take a break during the same period”.
A many-to-many relationship describes two entities sharing multiple relationships with one another, such as “Each employee can work on multiple advertising campaigns at any given time, and each campaign can have multiple employees participating in it at any given time.”
Rules we need to define the relationships in our database.
Each customer must have one representative
Each representative can have many customers
Each customer can have many orders
Each order can have many products
Each product can belong to many orders
As was previously mentioned, the Relationship tool include with Microsoft Access is simple ER diagram maker in itself. However, when working with the Access tool you are creating relationships on entities that directly correlate to tables in all cases, and you will need to explicitly define your associative entities .
Many other modeling tools exist for a variety of environments, such as the SQL Server Diagrammer and Microsoft Visio. An easy and cheap place to start though is with a pencil and a pad of paper.
One of the most valuable attributes of data modeling is that it can be used as a tool for the entire chain of project stakeholders, developers, investors, and managers. It is a powerful visual aid that anyone can understand the basics of, but is still of significant technical usefulness to the application developers.
No seasoned developer will tell you that planning ahead is a bad idea. While data models are incredibly helpful when drafting project requirements and collaborating on data system design, they are still part of a much larger systems analysis that should be completed for any project of significant proportions.