Originally Posted by admiraln
Take a jump to the left and not directly answering your question.
That's understandable. A redesign is impossible, but I enjoy what-ifs, which is why I asked this question.
The table has about 6 columns that make up a unique key, and then the value. The 6 columns are all foreign keys to resources in other tables. One of those is a resource that defines what the value is, or represents. So we can join to that table and identify if it's monetary or count.
The alternatives I see is to have two value columns, one for INT, and the other DECIMAL, or two tables that are identical beside the datatype for the value. I think its pretty obvious what the side effect of that will be. The application needs to be able to query multiple values for multiple resources, splitting them will require use of UNIONs to get the full results it requires.
It's pointless me describing exactly what this table does, as it's abstract and has little real world analogy.
But when making a judgement, you might consider the situation where a product that puts its settings in a 'settings' table with two columns, `settings`.`name`, `settings`.`value`.
Now, some settings within the product might store a string, some an integer, others a boolean. And some might be best represented buy an enum, or set.
I think with that example, you can see that the enforcing the correct DB design makes for a complicated set of tables. And when you think about it, what is the real world benefit? Splitting one table into many doesn't negate the need for the application to be very aware of what the values represent--it's not really normal to wrap application polymorphism around DB datatypes.
Rest assured, with the application I'm referring to, it knows when to treat a value as currency, or an integer. And these are the only two types it ever needs concern itself with.
The value can be large, they will sometimes crack 2 billion, so techically, we are using a BIGINT (unsigned), not an INT. And there are lots of rows--I think in some setup we've cracked 60 million. We don't select based on value. So there is no indexing on the value.
But take away the context of this DB, and consider the very simple question, where an INT "can" be used to represent a DECIMAL, what are the pros/cons of doing so?