A Quick Look at Entity Relationship Diagrams
To create a database, a developer must know what information needs to be stored, what tables are needed to store that information, and how each table is related to other tables. For example, take a database for a blog. A simple database may include two tables: users and articles. The users table may hold the user id, name, and email. The articles table may store the article id, timestamp, title, and textbody. For simplicity, let’s say one article can only be written by one user and One user can write many articles.
An entity relationship diagram is a graphical representation of the relationships of a database. Using ER diagrams help facilitate discussion and are especially useful when a database’s schema includes many tables. Given the blog example, an entity relationship diagram would look something like this:
Looking at each part of the diagram,
Rectangles: Represents tables.
Ovals: Represents an attribute of the table it is connected to. The primary key of the table is underlined.
Diamonds: A verb is generally used to represent the relationship between the tables.
1 — M: A type of cardinality constraint(discussed later); denotes one to many relationship between two tables.
For simple databases, it may not be necessary to create an ER diagram. However, if a client added more requirements at a later time, it would be useful to already have an ER diagram to build on.
What if the client wanted into include comments in each article? Well, we know that one article can have many comments and one user can write many comments so we can add a comments table that has a comment id, text, and timestamp.
Using an ER diagram as a visual aid simplifies the database creation process; the SQL queries for creating each table is modeled directly from the diagram.
Cardinality constraints show the relationship between two tables. There are a few types:
1 — M : One to many
N — M : Many to many
1 — 1 : One to one
This blog database example only includes one-to-many relationships, but can include a many to many relationship if one article can be written by multiple authors.
In the next blog post, I’ll discuss more about many-to-many relationships and self relationships.
ER Diagram Tool: https://erdplus.com/#/