Wednesday, May 20, 2009

To err is human

It's surprising how often database developers - or, at least, developers who are forced to develop a database - get really basic stuff wrong. Like choosing the correct data types, for instance.

I was asked to investigate a problem in an application database recently which, among other things, stored customer invoice details for the company. I was surprised to see that all the invoice line amounts, the VAT amounts, the discounts and so on were stored as float type data. I racked my brain to see if I could think of a good reason, but I couldn't. An invoice, and all the information on it, is a representation of real-world money, so you're never going to need anything more fine-grained than precision to two decimal places.

I can imagine, for instance, that you might have an Inventory table in your database, and you might record in it that each Yellow Widget weighs 0.023545 grams, or that the current sale price for each one is $0.1367, but by the time you're invoicing someone for 1256 of them, you're charging them $171.70 and not $171.6952.

We all make mistakes, though. Even in the future - check out this screen shot from The Terminator for another example of inappropriate data typing...

Labels: ,