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
AND COALESCE(fee_amount.joint, TRUE) = TRUE
AND fee_amount.joint = TRUE
withAND COALESCE(fee_amount.joint, TRUE) = TRUE
in your first query.