ERDSo, here's an ERD for the Oracle HR sample schema:
I do an ERD very early in the requirements definition phase of an application, and I refine it throughout the process. Why? Because this can help you discover things about your subject area and use cases that you don't get any other way. After all, the application is designed to help your business collect information about the things they do, and to use that information to inform and govern what they do next. Without data, your application is just a game. Not that I have a problem with games. And even games need to collect information about score, progress, items the player's avatar is carrying, etc.
Unfortunately, a very important part of an ERD is missing from this one - the names of the relationships. Relationship names help you to read and understand your diagram, and particularly to read the diagram to your users. What? You don't read your ERDs to your users? You SHOULD, if just because your users shouldn't be expected to read and understand the diagram for themselves. So the rest of this post is to explain why and how to read an ERD to your users. A lot of what I'm going to say is not original with me. I read an article a long time ago called "Why we say Each" which has guided my database designs ever since. Unfortunately, I have somehow lost my copy of the article, and have forgotten who wrote it. So to the author of this article - thank you.
Reading the ERDI'll start at the very top with the Departments entity:
- Each Department may be staffed by one or more Employees.
- Each Employee may work for one and only one Department.
Here you may begin to see why it is useful to read the ERD to your future application users. I would use this as an opportunity to ask them, "Is this correct - is it true that you can have a Department with no Employees? Is it possible to have an Employee who doesn't work for a Department?" Finally, there are the words, "one or more" and "one and only one". Once again, I can ask the users, "Can an Employee work for more than one Department at the same time?" Let's continue:
- Each Department must be managed by one and only one Employee.
- Each Employee may be the manager of one or more Departments.
- Each Employee may manage one or more other Employees.
- Each Employee may be managed by one and only one other Employee.