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)?

  • Google "ERD Circular References" or "Cyclic References". – SQLRaptor Oct 14 '19 at 23:38
  • Hmm I don't quite think this is a circular reference. Though it is putting me in the right direction (SO Google result: stackoverflow.com/questions/14999131/avoid-circular-dependency). A line represents a parent-child (top-bottom) relationship, sorry if that was unclear. – chakeda Oct 14 '19 at 23:59
  • I've not come across any specific term for this pattern. – Michael Green Oct 16 '19 at 0:55

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.

| improve this answer | |
  • Thank you for your detailed answer. Is there a terminology for this pattern though? – chakeda Oct 15 '19 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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