The Chasm and Fan Trap

As the name says itself, these are traps. If we have Chasm or Fan Traps, then we have a serious problem as these can impact the whole data model.

‘Chasm and Fan Trap lead to Cartesian Data.’

Fan Trap: In Fan Trap, the model ends up having ‘One to Many’ relationship in more than two tables e.g., One Customer having Many Orders and One Order having many Order Products which is logically fine but if physical data model is created like 1) Customer with Order ‘One to Many’ 2) Order with Order Product also ‘One to Many’ then it’s not fine. It’s a Fan Trap.

• Issue: In this scenario, Order Id (Pkey) has been added in Order Products tables as Fkey which means there will be Low Cardinality in Order products tables means there will be duplication of Product in Product’s table which is a wrong approach. In the Products table, Product ID should be unique.
• Solution: One Product can have Many Orders but not the other way. The correct approach is to have Product ID (Pkey) from Order Products table as Fkey in Orders table.

Chasm Trap: In Chasm Trap, the model ends up having ‘Many to One’ relationship in more than two tables e.g., One Employee having Many Sales Transactions and same Employee can have many Targets transactions as well which is logically and physically fine but if we try to join these three tables in ONE QUERY, it’s a Chasm Trap.

• Issue: In this scenario, the issue is not data model, issue is writing query. Consider there are employee names i.e., John in Employee table.
  • He has 2 Targets to achieve e.g., 1) For Product 1 = Target USD 1M, 2) For Product 2 = Target USD 2M.
  • Now, he has done 10 Sales Transactions in-total, let’s say USD 10k each transaction mean in-total as of now he has achieved USD 100k.
  • Now, extracting USD100k information via a query, if we join all these 3 tables in one query, this is what will happen.
  • Query will join Employee table with Target table and will process 2 rows in in-memory i.e.,
    • Row1: John with Target of USD 1M for Product 1
    • Row2: John with Target of USD 2M for Product 2
  • Now, Query has 2 rows in in-memory to join with Sales table. Now, it will join these 2 Johns with Sales table using John as key, this is what will happen.
  • John [Product 1] will join with 10 rows in Sales and will generate 10 Rows.
  • John [Product 2] will join with 10 rows in Sales and again will generate 10 Rows.
  • So, in total there will be 20 rows generated which is wrong as now total target achieved will show as USD 200k instead USD100 which is wrong.
  • The issue is Cartesian Result.
• Solution: There are two approaches to handle this scenario 1) write two queries separately 2) write 2 queries with-in 1 query and use UNION ALL. Let’s choice 2nd Approach and understand with an example.
  • 1^st Query will join Employee table with Target table and will process 2 rows in in-memory i.e.,
  • Row1: John with Target of USD 1M for Product 1
  • Row2: John with Target of USD 2M for Product 2
  • 2^nd Query will join Employee table with Sales table and will process 10 rows in in-memory i.e.,
  • Row1: John with Sales of USD 10k for Product 1
  • Row2: John with Sales of USD 10k for Product 2
  • .
  • .
  • Row10: John with Sales of USD 10k for Product 2
  • Now, join both queries using UNION ALL and you will see 12 rows.
  • Correct result.

The first step towards creating a data model should be to understand the flow of data which defines data storing.

The first step towards extracting data is to have business knowledge of data models.

‘One must not forget to have transactional table to be ‘Many’, and Master/Reference table be ‘One’ in the equation.’