Is it OK to use the same field in the database to store both a percentage rate and a fixed money fee?
I am trying to convince a colleague that their proposed approach to storing a "fee" is problematic. The "fee" can be either a percentage rate, e.g. 50%, which is stored in the database as 0.5 (float) or a fixed money amount, e.g. $100, which is stored in the database as 100.00 (float). I already know that storing money as a float is fatal. I do not want to discuss this. The proposed approach is to store both the percentage rate and the fixed money amount in the same field. Then there is another filed called fee_type with enum value PERCENT or FIXED which tells us how to interpret the float. I think this is problematic and creates undue complexity. I would rather put the % and fixed fees in separate fields. Can I please get some validation or contradiction on this? Am I crazy for thinking that it is wrong to use a single database field to store what is sometimes a % and sometimes a money? My main 3 reasons are: more future-proof (if we ever fix the horrible money-as-float problem, these fields will have to be split anyway) safer (avoids problem of fee being 20000%, for example, which the data could technically allow) simpler UI implementation (this is a little bit subjective but I can not imagine a UI where it is not simpler to have the % and the fixed fee in separate fields). UPDATE: I know it is also wrong to store the % fee as a float. I wish now that I had lied and said they are both stored as decimals, to avoid that discussion here. :)
I am trying to convince a colleague that their proposed approach to storing a "fee" is problematic.
The "fee" can be either
- a percentage rate, e.g. 50%, which is stored in the database as 0.5 (float) or
- a fixed money amount, e.g. $100, which is stored in the database as 100.00 (float).
I already know that storing money as a float is fatal. I do not want to discuss this.
The proposed approach is to store both the percentage rate and the fixed money amount in the same field. Then there is another filed called fee_type with enum value PERCENT or FIXED which tells us how to interpret the float.
I think this is problematic and creates undue complexity.
I would rather put the % and fixed fees in separate fields.
Can I please get some validation or contradiction on this? Am I crazy for thinking that it is wrong to use a single database field to store what is sometimes a % and sometimes a money?
My main 3 reasons are:
- more future-proof (if we ever fix the horrible money-as-float problem, these fields will have to be split anyway)
- safer (avoids problem of fee being 20000%, for example, which the data could technically allow)
- simpler UI implementation (this is a little bit subjective but I can not imagine a UI where it is not simpler to have the % and the fixed fee in separate fields).
UPDATE: I know it is also wrong to store the % fee as a float. I wish now that I had lied and said they are both stored as decimals, to avoid that discussion here. :)
