Entity Relationship Diagram

ERD:

Draw a box for each table in your relational database.

For each ForeignKey that exists in any table... Draw a line from that table to the table with the corresponding PrimaryKey. Decorate the line to indicate cardinality of the relationship, and possibly to give it a name.

(Older forms of ERDs used diamonds and other shapes to represent relationships.)

A relational structure to represent ER diagram(s):

  Table: Fields
  ---------------
  tableName
  fieldName
  (other field info....)

  Table: Relationships
  ----------------------
  fromTable
  fromField
  fromRelationMin  // (0,1,N)
  fromRelationMax  // (0,1,N)
  toTable
  toField
  toRelationMin  // (0,1,N)
  toRelationMax  // (0,1,N)

  Table: Tables
  -----------
  tableName
  (other table info....)

Note that ER diagrams are not intended to cover every possible relationship between entities, but just those that can be known from the keys. (CalculatedRelations)

Hmm. This page describes ER diagrams as purely derivative of a database schema. However, that's far less interesting than their more common use, which is as a tool for discussion of the relationships that need to be modelled by a schema which is under design. ER diagrams are easy for non-technical people to understand, and thus are typically used by database designers before the schema ever exists. And the final schema often contains many relationships, both explicit (as for foreign keys) and implicit, that never appear on an ER diagram. There are many considerations that go into the final schema, and in fact there's no guarantee that an ER diagram, even though accurately describing the purpose of a database, will relate in a simple way to the final implementation.

Are you saying that ER diagrams are *only* for planning?

Well, I looked over what I wrote, and no, I didn't say that...

I read it again, and that is indeed what it seems to imply. A 3rd opinion anyone?

OK, let me help you read it then: "... that's far less interesting than their most common use, ..." That hardly implies that ER diagrams are only for planning. However, that is their primary use, and I wanted to point out that it's dangerous to assume that an ER diagram could or should be able to completely describe a database schema. A typical schema of a large database, if completely described by an ER diagram, would usually result in a picture so large and complex that it would lose much of its utility as a simple, intuitive presentation of key relationships.

Having said that, I frequently use ER diagrams in exactly the way described at the top of this page in order to help me understand a piece of a database schema that I am not familiar with.

If you truly believe that the wording above was misleading, please correct it rather than arguing with me about it -- I quite deliberately left it unsigned. -- DanMuller

Okay, lets see if I can paraphrase your viewpoint correctly: An ER diagram is mostly used for planning or studying a specific sub-set of the schema, but is otherwise usually insufficient to represent a significantly large portion of the entire schema. I guess I generally agree with that.

The "diamonds" in ER Diagrams often imply intermediate tables to store information about the relation if needed. For instance [Customer]--<buys>---[product] could have a "buys"or purchases table with CustomerID,Date,ProductID to store the history of purchases and only store info about Customers (names, address etc) and products (modelNo, cost, description) in the other tables. Diagrams with only tables and 0..n lines between them are designated IDEF1X (or similar). ER diagrams are at a higher level of abstraction. In the example above Purchases would then become a rectangle in the IDEF1X diagram. See http://www.essentialstrategies.com/publications/modeling/idef1x.htm ClassDiagrams in the UnifiedModelingLanguage (UML) also map to relational diagrams for objects that need to persist (not necessarily 1-1) see ObjectRelationalMapping. It is because there can be many, many classes/tables that UML defines PackageDiagrams? to try partition the design space so you don't have to look at everything at once. However it is not unreasonable to want to see lots of them together if you have ever seen an Engineering diagram (Circuit Board, Power or Manufacturing Plant, etc) on (one or more) A4 paper(s) the quantity of information for components (resistors, ICs, capacitors etc or tanks, valves, motors etc) and lines (wiring or pipes, conveyors etc) between them is comparable to what it would be if you printed out a complete detailed class or database design.