The purpose of E/R-diagrams is to give a quick overview of the relevant database. It is important, that all tables are there, but even more important, that all relations are there. In this area tables are called entities, and the system of foreign keys are called relations - entity-relation-diagram. Ideally entities have their own life - postal districts and members have a life of their own, whether they appear in a database or not. Relations are more fluid - they don't always have a real physical existence.
On the left there's an example of an E/R-diagram. The tables are boxes, and the relations are diamonds, with lines to the tables, there's a relation between. The numbers on the connecting lines show what kind of relation, this is - also called multiplicity. The relation on the right is a one-to-many relation. It is read as: "one player can write many messages". The relation on the left is a many-to-many relation - one player can be in many chatrooms, and a chatroom can contain many players. It's also possible to have a one-to-one relation. You don't reach this relation by normalization, but can choose it for optimization. E.g. a table can have columns often used (name and password is used at every login), and other columns almost never used (a users gender and birthday). In this case the table can be split, so the columns often used are apart.
A special kind of E/R-diagram is called Bachman-diagram. As many-to-many relations become new tables, it's not allowed to have many-to-many relations in Bachman-diagrams - they must also be shown as tables. So this kind of diagram is a bit closer to showing the real look of the database.
E/R-diagrams can be more slim than shown here. Names of columns and relations don't have to be here. But names of tables and multiplicity should always appear.
Concept last updated: 07/07 2003.