Consider the following "Diamond-Shaped" entity relationship diagram:

Diamond Shaped ER Diagram

I'm trying to find some formal readings on why this is a bad design - but Googling "Diamond Shaped Table Relationship" only turns up the ERD symbol.

What is this relationship called? Why is it a bad table design (if it is)?


2 Answers 2


You'd have to add the crows feet (or whatever notation you prefer) for us to properly understand the diagram. I choose to interpret it as directed top-to-bottom.

    top table
     |     |
     ^     ^
   Left   Right
      |    |
      ^    ^

Fig: Diamond pattern

I do not recall ever seeing this pattern explicitly named. "Diamond" seems as good as any.

As such I don't believe it is a circular reference. For that you'd have to be able to follow the "many" back to the origin:

  First ---< Second
    v           |
    +-- Third >-+

Fig: Circular reference

I question the premise that the diamond pattern is a bad design. For certain applications it may be entirely appropriate. Perhaps it's just not appropriate for yours.

As a toy example consider a small multi-tenant app to track hot-desking. We have entity types Company, Employee, Desk and DeskAllocation. A Company hires many Employees. The Company provisions many desks. Each employee can sit at a different desk each day. Naturally an employee may only sit at a desk provisioned by their employer.

We can identify the company by it's business registration number or a made-up number we supply. It doesn't matter. We'll call this CompanyId.

Companies create employee IDs for their hires. Since every company has an employee who wants to be hire #1 we have to have a composite key of (CompanyId, EmployeeId).

Each company has its own labeling scheme for desks but most of them use a simple numerical sequence. So the PK of Desk is the composite (CompanyId, DeskId).

So what's the PK of DeskAllocation? It will be the PK of Employee, that of Desk and a date column. Those first two are themselves composite, each containing CompanyId. That may seem redundant, or even an error, but is integral to the enforcement of the "sit at your employer's desk" rule. That can be implemented as a check constraint.

The pseudo code for DeskAllocation will be

table DeskAllocation

  primary key

  foreign key FK_Desk
  (DeskCompanyId, DeskId) references Desk(CompanyId, DeskId),

  foreign key FK_Employee
  (EmployeeCompanyId, EmployeeId) references Employee(CompanyId, EmployeeId)

  constraint CHECK DeskCompanyId = EmployeeCompanyId

This is may seem artificial but I have implemented exactly this pattern in an application for automotive pricing.


Hell is the formal term. Maybe try to use a star pattern, similar to DB warehouse. Look for "patterns and anti-patterns for database schemas." If you do program a diamond, I suggest pre-populating your list of value tables with an admin interface, not your production data loading interface.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.