How to Turn Requirements into an ER Diagram (Find the Entities) in Visio
Nouns become entities, verbs become relationships. Learn a simple way to turn a written requirement into a crow's foot ER diagram in Visio, with a library loan as the example.
Step by step
- Requirements to ERD. You have a written requirement and need a data model. A simple way in: the nouns become entities and the verbs become relationships.
- The requirement. The requirement: a member can borrow many books, and a book can be borrowed many times over the years. The shapes come from the Crow's Foot stencil in the Shapes pane.
- Nouns are entities. Start with the noun member. It becomes an entity called Member, with its key, MemberID, and the details we keep: Name and Email. Then the noun book becomes an entity called Book, with BookID, Title and ISBN.
- Check the verb. Next, check the verb, borrow. A member borrows many books, and a book is borrowed by many members. That is many-to-many, and a relational database can't store it directly.
- The verb becomes an entity. So the verb becomes its own entity, Loan. Each loan is a single borrowing. It holds LoanID plus both keys, MemberID and BookID. The loan dates also belong here.
- Relationships. Use the Relationship connector to join Member to Loan. Each member can have no loans or many loans, so the crow's foot end goes at Loan. Do the same from Book to Loan. Each book can appear on no loans or many loans.
- Check it out loud. Read the model back against the requirement. Can a member borrow many books? Yes, through many loans. Can a book be borrowed many times? Yes, through many loans too. The model matches.
- Done. Click an empty part of the page to deselect the shapes and see the finished model.
- The method. The method: underline the nouns and make them entities. Check each verb, and if it is many-to-many, turn it into an entity that holds both keys. Then read the model back against the requirement.
Common mistakes
- Drawing a many-to-many line straight between Member and Book. A database can't store that link directly. Add a Loan entity in the middle that holds MemberID and BookID, and connect each side to it.
- Making every noun an entity. Some nouns are only details of another thing, like email or title. Make them attributes of Member or Book instead of separate entities.
- Leaving the keys out of the link entity. Loan only works if it holds MemberID and BookID. Without them, there is no way to tell which member borrowed which book.
- Skipping the read-back. Read each line of the requirement against the diagram before sharing it. If you can't answer yes to each question, a relationship or entity is missing.
When a business analyst uses this
After a requirements workshop for a booking system, a business analyst underlines the nouns in the agreed requirements, such as customer, room and booking, and sketches the ER diagram in Visio. Reading it back with the stakeholders shows gaps before the developers design the database.
Questions people ask
How do you find the entities in a requirement?
Underline the nouns. Things the business keeps data about, like member and book, become entities. Nouns that only describe something, like email, become attributes.
How do you resolve a many-to-many relationship in an ER diagram?
Add a new entity between the 2 sides that holds both their keys, such as Loan holding MemberID and BookID. Each member and each book can then appear on many loans.
What is a junction table?
It is a table that sits between 2 entities in a many-to-many relationship and stores both their keys. In this example, Loan is the junction table between Member and Book.
