Mysql – How to normalize a lookup table containing NULL? – or should I bother

MySQLnormalization

I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what description the fee is for. What is the best way to normalise the two given the issue I describe later?

The original data

fee
===            
fee_id | description
-------------------------        
1      | subscription        
2      | joining

and 

fee_amount 
==========           
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35

To obtain the amount I need to invoice I join the tables fee and fee_amount on the fee.id and look up the fee amount from the fee_amount table using the keys of fee_amount.year, fee_amount.joint and fee.description. i.e something like

SELECT amount 
FROM fee_amount 
JOIN fee ON (fee_amount.fee_id = fee.fee_id) 
WHERE 
   fee_amount.year = 2020 
AND 
   fee_amount.joint = TRUE 
AND 
   fee.description = 'joining'

The problem

I introduce a new fee description 'booking', whose amount only depends upon the year i.e. I don't care if it's joint or not. This gives rise to N/A or NULL in the fee amount lookup table as shown below which rather complicates looking up fee amounts.

What is the best way to normalise this to avoid the NULLS? or shouldn't I bother and just have a more complicated SELECT.

Example new data with a fee type that doesn't require a 'joint' value.

fee
===            
fee_id | description
-------------------------        
1      | subscription        
2      | joining        
3      | booking


 fee_amount 
==========           
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  NULL |  77
   3   | 2020 |  NULL |  88

Edit… Or maybe should I simply add two rows in the fee_amount table for the same year, one with joint = true and one with joint = false, both with the same amount and then it won't matter what I put in the WHERE clause for fee_amount.joint
ie

 fee_amount 
==========           
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  TRUE |  77
   3   | 2019 | FALSE |  77
   3   | 2020 |  TRUE |  88
   3   | 2020 | FALSE |  88

Best Answer

Perhaps a better policy:

  • When registering a user, store the fee charged into the user's history.
  • Derive the fee from some business logic that may or may not be stored in a database table.

Note how this policy freezes the fee as soon as it is billed. This is safer than having it in some other table that might accidentally be changed for unrelated reasons.

Related Question