Your analysis is correct.

Beyond the complexity issue I would add as problems:

• The design is misleading and error-prone as it could give the impression of handling in a uniform way the fee field.
• It makes it very cumbersome to use typical report generators and query tools on this data, considering the different unit of measures, the different aggregation rules, bit also the different precision/display rules.
• Moreover, in an internationalised version, amounts are to be dealt with a currency and a conversion factor whereas this would not be relevant at all for the percentage.

The two-field approach overcomes all these limitations and is hence future proof. The two-field approach is also an implementation of the single table inheritance pattern, which is a proven approach to deal different kind of fees (specialization/inheritance with different attributes) in a single table.

Now in all honesty, multiplexing the two kind of fields in a single floating point field can work if your app is the only one that accesses it. It’ll just require extra care to maintain it and limits the flexibility.

There’s a much simpler approach here that should bypass any petty database schema design arguments. Your business logic is really this:

TOTAL_COST = (BASE_COST * (1 + PERCENT_FEE)) + FLAT_FEE

Coincidentally in your case, exactly one of PERCENT_FEE and FLAT_FEE is always zero. In this model, we only have one branch of logic instead of two.

• Put both PERCENT_FEE and FLAT_FEE columns on your database
• Give them both a default value of 0
• (Optional) Include a check constraint to ensure exactly one is zero
• (Optional) Declare a view with the total cost already computed

Each column means exactly what is says, two numeric columns won’t use any more disk space than one numeric and one string, and you’re in pretty good shape when your boss asks you how hard it will be to support a formula with both types of fees. The data model already supports it and the calculation logic can be centralized in one place.

For that reason, I wouldn’t bother with the constraint on the database table. At most, enforce it in the UI/API before it gets to the database.

This might be overkill for your needs here, but another option that you might not have considered is to separate these values into two separate child tables e.g. PERECENT_FEE and FIXED_FEE which have foreign keys pointing back to the parent table.

This avoids the need to have null fields and a ‘checkboard’ table. As I mentioned above, this may be over-engineered for your needs, but it can be a useful approach when you have heterogeneous data structures.

more future-proof (if we ever fix the horrible money-as-float problem, these fields will have to be split anyway)

I don’t see why this is necessarily the case.

Financial percentages should no more be stored as floats than absolute financial amounts should.

The core use for floats is physical quantities, where exactness is not necessary, where it is desirable that the inexactness be proportional to the overall scale, and where it is more desirable to have a wide range of scale factors than a monotonic interval between each representable value.

The use for decimal or fixed-point is where exactness is necessary, where precision must be fixed, and where it is more important for every value within a range to have an exact representation than for the range of scale factors to be wide.

So here, both values should be moved over to using a decimal type.

safer (avoids problem of fee being 20000%, for example, which the data could technically allow)

Ease of validation and enforcement of constraints may be a reason. I’d be surprised if there’s much in it though.

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).

To be honest I can imagine both approaches to the UI. One where there is a drop-down and a single numeric field, another where there are two numeric fields but only one can be filled.

Summary

I don’t think there are any decisive reasons one way or the other that always apply.

The choice of how to represent the cases of a union type might be governed by whether there is ever a difference in data type between the cases anywhere in the database, or might be in future, with any difference in data type tending to require separate fields (unless you venture into the realm of “variant” types, or coerce everything to a common denominator like a string representation).

Generally you’d want to be consistent in the approach across the database and across your own practice, if possible, or at least consistent in the default approach which is then only varied for reasons in specific cases.

Another aspect of the choice might be how many cases have to be represented, with more cases of the same data type tending towards sharing fields rather than having an extremely wide table.

Arcane performance criteria might also govern at the extremes. For example, how the desired indexes will work.

My snap judgment is that separate fields would be more flexible in general, but I wouldn’t dig in over the issue, especially if someone else is doing the design.

I agree with Christophe’s answer for the reasons they have stated, however I think its also worth thinking about the alternative (Enum + Value) solution so that we can refute any arguments that support the enum solution, specifically:

Disk Space

Since both solutions have two fields and in both cases you will need to load both fields, there isn’t going to be a huge performance difference, technically with a small enum it may be possible to optimize the number of bytes on disk, however bluntly “disk space is cheap” so I don’t think you can seriously make that argument to support the enum solution.

Invalid Storage State

A valid argument against the “separate” fields is that the data stored on disk can be in an invalid state, either both fields are set or neither field is set. The enum solution can avoid this with NOT NULL constraints on both fields. This argument can be mitigated (in the separate fields solution) by including a check constraint to prevent a record from being inserted (or updated) with an invalid value – I would suggest that it good practice to enforce data integrity at the database level, in this case and more generally whenever it can be enforced without significant penalty.

ORM

I suspect that the supporter of the enum solution is probably pitching that solution, because the database representation more closely matches their in memory representation (read: they are using an enum in their object model).

This is a bit more tricky to refute, primarily because we have to decide what our argument is, there would appear to be two main choices:

• Argue that the in memory representation shouldn’t be an enum either.
• Argue that we should map the on disk representation to a different one as part of the (de)serialization process.

With only two fee types, the code for either of these options is not going to be particularly complex, however I can see someone making a “what if we need to expand the number of types” argument. In that having an enum means that the logic for calculating the fee is just switch (based on the enum) and serialization is trivial, if the in memory and DB representations are identical.

I would be tempted to shut down this argument by saying “How many fee types can you support with just an enum and a float field?”, in that I accept that additional fee types are possible, but it’s likely you will need additional fields (such as min quantity for a reduction in fees) to support additional fee types.

Hence the crux of the argument is that having separate fields is the best solution for the problem we currently have and we should consider alternatives if/when we need to implement them (read: we have the full set of requirements).

That last point is a little too hand wavy for my liking, it opens up an argument that the simplest code possible today is a simple enum and direct serialization of that enum into the database.

Given that I am tempted to suggest mapping the separate fields in the DB representation to an enum during the (de)serialization process, based on an argument that it gives us the most flexibility to evolve both the storage and processing models independently as new requirements emerge.

Describe how you do the following:

1. Decide whether you have a percentage or an absolute amount.
2. Return the absolute amount, no matter what is stored.
3. (Possibly) Return the percentage, no matter what is stored.
4. Change what is stored to a percentage, or to an absolute amount.
5. Make sure that nothing invalid is stored. For example two values.

Now you implement these as functions, and never ever in your life access the fields individually. Decide which data in the database are faster and more space efficient, and that’s what you use.

I forgot: If you store a percentage then it must be a percentage of something. For example 20% VAT is a percentage of the cost of an item. If the item costs £99, then 20% and £19.80 are the same amount. However, if the item price changes to £98, then you can leave the percentage unchanged, while the absolute amount changes to £19.60. So you need to make sure that when the item price changes the absolute amount also changes in your database. But if you store the percentage, changing the item price can still change derived values, for example the total amount of VAT on a bill.