|These database tools address specific needs and can save you a great deal of time. The tabs after the next one provide you information that may also help you in your database work.|
Follow these database principles to save time and frustration:
| When you design your application and database, most of the energy and time goes into how to load new entries into the database (or processes to modify the data already there). |
But you also need to think about what to do when your data need to be removed. While it might seem simplistic just to delete those records you no longer need, this can cause other problems.
What do you do with those transactions when you want to delete the customer or product? If the master record is no longer there, the transactions may disappear from your reports. If you also delete the transactions, then the historical totals will be incorrect.
A safer approach is to include a field to indicate obsolete or deleted records. Keeping that historical data in your database will also keep your auditors happy. You just need to remember to exclude the deleted records from your reports. That is just a simple record selection rule to include in your report. Another approach is to create a separate, archival database for obsolete records. Export the records you want to archive, then delete them from the database.
| Date Time fields in our databases include both a date and a time component. We are all pretty clear about the when things happen at 2am or 3pm. But times like midday and midnight can be confusing. It's usually best to use a 24 hour time format. |
The Time component can be displayed in several formats, so we recommend you select a format and hard code it in your report. Regional settings can vary from machine to machine and this can change the way they display to different users.
Midnight is a time value of 00:00:00 and midday is 12:00:00. This will appear as 12am and 12pm respectively. The place it causes confusion is when you add ten minutes to 11:58am to get 12:08pm, which is followed by 1:08pm an hour later.
To avoid confusion, always use 24 hour time, and keep away from that am/pm format. It is too easy to slip into the wrong 12 hour period. This applies to both field display and parameter input in your reports. There is an exception, however. Many people are uncomfortable with a 24 hour clock. This is probably the only reason to ever use a 12 hour clock. It's a concession to custom. That's why we still see 12 hour times given on Websites that operate 24 hours a day and in all time zones.
| If you are working with date time fields and care about business hours between 7am and 7pm (or slightly later) only, then regular date time fields with local time will be fine. |
But if your clock runs 24 hours and/or 7 days a week, and you cover multiple time zones, or have daylight wasting time, then you need to be careful. Real-world examples of such environments include air travel, factory processing, and energy billing.
You will need two date time fields in your database. One with the local time, and one with the time in UTC (Universal Time Coordinates). UTC is what we used to call Greenwich Mean Time (GMT). There are two hours a year when you cannot do accurate time variance calculations using local time only. These are the hours when daylight wasting moves forward and back--they will make these calculations wrong. You need an equivalent period without the daylight wasting component. UTC is the solution to this.
| We rely on our databases for accurate calculations. We like to see information generated "on the fly" from raw data. But a bias among managers, going back to the early days of spreadsheets, can lead us to thwart this process and introduce inaccuracy. |
For this tip, we're going to look at spreadsheets. You can apply the lesson to your particular database program, and you may need to apply it to the spreadsheets that feed those database programs. Today, Excel is the dominant spreadsheet application. Though Excel is in wide use (or rampant use, depending on your viewpoint), many users don't understand the basics of the program. When it comes to rounding, that is certainly the case.
By default, Excel rounds a number for display purposes but uses the unrounded number for calculation. This means you may see an "error" in the math--but what you are really seeing is display vs. actual. A way to fix this is to increase the number of digits displayed. But the display is really for reference. What counts is the calculation.
If you use the ROUND function before the final calculation, you make display and actual agree the whole way through. But then you are losing accuracy at every step by forcing Excel to work with the rounded numbers.
Let's say you keep this in mind, and thus don't use the ROUND command except for the final number. (If you use currency formatting, you don't need to use ROUND at all). That is, you don't use ROUND during calculation. This will result in a maximum error of less than 0.49999 cents (we can assume 5 decimal places is adequate, for purposes of illustration here).
So, whether you have 10 rows or 10,000 rows, your maximum error will be statistically insignificant.
But, that's not true if you are rounding during the calculation process. The maximum error, if using the ROUND function for each row would be (0.49999 * N), where N is the number of rows. So for 20 rows, the maximum error is then $10.
Excel is good to 255 digits (for obvious reasons--256 being the square of 16). After that, it simply truncates the number at the least significant digit (I'm 99% sure about the truncation, rather than rounding). You can display all 255 digits, if you choose. For financials, you should choose not to--that follows the aesthetics convention of showing only two decimal places.
But aesthetics and calculation are going to have differences. It's the calculation that you want to rely on.