Data-type consistency is not a concern with database schema design alone, it of course applies to any code you write, especially in a "strong typed" language.

When I say data-type consistency, I'm talking about the need for fields or variables which carry the same type of data to have the same data-type.

This can be especially important in a database schema, as we're more likely to suffer from large amounts of unneeded storage space.

But, the larger issue comes with performing logic operations on that data.

Let's take, for example, a MySQL database where a price column is set as a DECIMAL(8,2) in one table, and a FLOAT in another table. A comparison like

table1.price = table2.price

WILL NOT work, even if the value appears to be the same.

Using the above as an example, if a DECIMAL(8,2) is the most appropriate data-type for your data, it should be used consistently in place of a FLOAT type, not only for comparison purposes, but because it requires less storage space.

In looking at database schemas over the years, I'd have to say the most common area where I see this is with foreign keys. One table will have a primary key with data-type SMALLINT, where another table will relate with a foreign key whose data-type is INTEGER. While logic operations will work properly in this case, there is quite a bit of wasted storage space on the INTEGER field.


Leave a Reply

Your email address will not be published. Required fields are marked *