“Diamond Shaped Relationship” What’s the formal term

database-designerdterminology

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

Best Answer

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
      |    |
      ^    ^
      Bottom

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
(
  DeskCompanyId,
  DeskId,
  EmployeeCompanyId,
  EmployeeId,
  AllocationDate

  primary key
  (
    DeskCompanyId,
    DeskId,
    EmployeeCompanyId,
    EmployeeId,
    AllocationDate
  )

  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.

Related Question