Editing Entity–relationship model (section)

== Model usability issues ==

{{expand section|fan trap causes|date=February 2018}}
Users of a modeled database can encounter two well-known issues where the returned results differ from what the query author assumed. These are known as the [[Entity–relationship model#Fan trap|fan trap]] and the [[Entity–relationship model#Chasm trap|chasm trap]], and they can lead to inaccurate query results if not properly handled during the design of the Entity-Relationship Model (ER Model).

Both the fan trap and chasm trap underscore the importance of ensuring that ER models are not only technically correct but also fully and accurately reflect the real-world relationships they are designed to represent. Identifying and resolving these traps early in the design process helps avoid significant issues later, especially in complex databases intended for [[business intelligence]] or decision support.

=== Fan trap ===

The first issue is the fan trap. It occurs when a (master) table links to multiple tables in a one-to-many relationship. The issue derives its name from the visual appearance of the model when it is drawn in an entity–relationship diagram, as the linked tables 'fan out' from the master table. This type of model resembles a [[star schema]], which is a common design in data warehouses. When attempting to calculate sums over aggregates using standard SQL queries based on the master table, the results can be unexpected and often incorrect due to the way relationships are structured. The miscalculation happens because [[SQL]] treats each relationship individually, which may result in double-counting or other inaccuracies. This issue is particularly common in decision support systems. To mitigate this, either the data model or the [[SQL query]] itself must be adjusted. Some database querying software designed for decision support includes built-in methods to detect and address fan traps.

=== Chasm trap ===

The second issue is the chasm trap. A chasm trap occurs when a model suggests the existence of a relationship between entity types, but the pathway between these entities is incomplete or missing in certain instances.

For example, imagine a database where a Building has one or more Rooms, and these Rooms hold zero or more Computers. One might expect to query the model to list all Computers in a Building. However, if a Computer is temporarily not assigned to a Room (perhaps under repair or stored elsewhere), it won't be included in the query results. The query would only return Computers currently assigned to Rooms, not all Computers in the Building. This reflects a flaw in the model, as it fails to account for Computers that are in the Building but not in a Room. To resolve this, an additional relationship directly linking the Building and Computers would be required.