Two clients in the last several weeks have shown up with the exact same issue and it’s time to talk about it. In both cases the clients had a field in a table that could contain multiple sets of data. This is a really bad idea and if you find yourself doing it…well…stop it!
In the first case their company table has an email field and at some point in their past they decided that some users needed two email addresses. Instead of creating an additional field for an alternate email address they simply decided to concatenate the data into one string separated by a comma.
To be honest I’m not sure why it sounded better to concatenate the data rather than create a second field but that’s what they did. This decision was made years ago, of course, by a developer no longer working for the company, but it’s now up to me to ‘fix’ it.
With emails being prolific it might make more sense to have an email specific table that tie to the customer and can be inactivated. This solves a number of issues. It lets users have more than one email addresses. The other thing it does is keep a history of the customer email addresses so if you are trying to verify a user account via the phone it might be a way to verify their identify when all else fails (not that I’d solely use that).
In the second case the customer table has several fields that can grow over time. One field has notes separated by carriage returns, another field has billing data and another has payment data and each has ‘records’ in that single field separated by carriage returns and the data in each record separated by pipes.
Example: The notes field for a single customer might be something like this:
6/20/2010 This is a note
6/30/2010 This is another note
7/5/2014 This is another note that could be pages long.
If you find yourself designing your database like this STOP right now and step away from the keyboard! Databases are really good at having tables with related data. Your notes, bills, and payments tables would all have a foreign key references back to the customer table. That way you can have as many of those children tables as needed without affecting the customer table. Having fields that grow exponentially in a single record is a bad thing.
Another issue that I see a lot is that you, the programmer, should never, ever, generate your own record id’s. Let the database do that unless you have some really special-use scenario where you can do a better job of it. To be clear, I’ve never seen this scenario. Instead, your primary key should be an auto-incrementing integer and is something that you should never be able to modify. That’s not to say that you can’t create your own ‘human readable’ code but it should never be used as the primary key of your table.
Another thing, take the time to use the native data types for the database. If it’s an integer use an integer field type. If it’s a date then use a Date or DateTime field type. Booleans, if not a native data type for the database you’re using, can be a TinyInt with a length of 1. Your conversion to and from Xojo will not be an issue and you let the database do a tremendous amount of work for you. One client had an Amount field set up as string. To get a total amount they had to load the entire recordset in, loop through it, convert the string to a double and keep a running total. Instead, they could have done a simple Sum in an SQL statement and let the database do all the work! Trust me, it’s much faster that way.
Those are my database hot button topics. My DBA wife (i.e. The Database Goddess) has her own hot button topics and has
beaten them out of me convinced me of the error of my ways and I no longer do them (or at least not without a reprimand).
In most cases the clients didn’t know any better and I’m sure at some point in my distant software development past I did some silly things like that too. Those silly bad habits were beaten out of me after several accounting projects where database speed was essential.
What sorts of database blunders have you seen that now drive you nuts?