0

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
6
  • What is the best way to normalise this to avoid the NULLS? AND COALESCE(fee_amount.joint, TRUE) = TRUE
    – Akina
    Jan 21, 2020 at 12:39
  • Thank you. I'm sorry, I do understand what COALESCE() does but I'm not sure how you are suggesting that I use it in a single SELECT to get an amount that is either WHERE joint is a specific value or WHERE joint can be either true or false as I don't care. Is it possible you could post and example? (and which table data does your suggestion refer to, the one with a NULL or the one where I insert an artificial value for joint? Jan 21, 2020 at 16:55
  • I am disappointed that you appear aggressive. I said I DO understand what COALESCE() does - it returns the first non-null value in its argument list. That's why I have not searched the documentation and have not read the function description (at least not again recently). All I was asking you for is a little more explanation regarding how you thought that function would be handy in this situation. If you know an answer I'd be very grateful to see it. Jan 21, 2020 at 19:03
  • I don't understand why you cannot simply replace AND fee_amount.joint = TRUE with AND COALESCE(fee_amount.joint, TRUE) = TRUE in your first query.
    – Akina
    Jan 21, 2020 at 19:26
  • @Akina I see you have deleted your comment in response to me asking for an example (ie. your comment simply asking why I had not searched the documentation on COALESCE() or read the function description.) I guess you thought I said don't understand instead of do understand. Jan 21, 2020 at 19:26

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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