I need to create a table to record many different types of fees for a given order. The issue is that there are about 10 types of fees (and these types may increase outside of my ability to control them) however they are mostly 0.00.
The linear table structure would look like this:
A column is created for each fee & fee currency & the table would just extend outwards. There are no joins & is simple but I store a lot of data which is meaningless.
OrderID Date Buyer Total TotalCurrency Fee FeeCurrency Giftwrap GiftwrapCurrency ShippingFee ShippingFeeCurrency 123456 42501 John Smith 500 USD 20 USD 0 USD 2 USD 987654 42501 Joe Blow 499 GBP 19 GBP 1 GBP 0 GBP
The dynamic table structure would look like this:
It has two tables: Order & OrderCharge. The order charge only stores the values that are greater than 0 and all missing fields are queried as $0. New charges can be added without an issue.
OrderID Date Buyer 123456 42501 John Smith 987654 42501 Joe Blow OrderID FeeType FeeValue FeeCurrency 123456 Total 500 USD 123456 Fee 20 USD 123456 ShippingFee 2 USD 987654 Total 499 GBP 987654 Fee 19 GBP 987654 Giftwrap 1 GBP
The part that I'm worried about - which I don't know - is if the extra workload added by joining/pivoting a table is greater than the amount offset by not including useless data.
Is this actually better? Or am I making it both more complicated & worse?