Join Adam Wilbert for an in-depth discussion in this video Preventing data conflicts, part of Learning Relational Databases (2014).
Another source of common data conflicts occurs when stored values are simply calculated from other information that you're already keeping track of. The problem is that if one value changes, then we'll need to be sure to update the calculation as well. Here's an example. Let's take a look again at the invoices for our olive oil sales. We can see that Delish Foods purchased two bottles of our first cold press oil. If we look up the price in the products table we can see that first cold press is $10 per bottle. So the invoice correctly shows the total due is $20.
This is good and accurate information for now, but what happens if something changes? If the order quantity gets changed, say from two to four then we will also need to have the database management system update the total price as well to reflect the new total due is $40. Or if the price per bottle changes in the products table to $15, then again, the total price in the invoice table would need to be updated again. So we have two different situations where a change in one field on the database causes a change in another. And sometimes, that happens in completely separate tables.
If the second change doesn't happen, then we have a data conflict where the total price doesn't equate with the quantity ordered and the price per unit. The solution to this problem might be a little surprising. But it's to simply stop storing that kind of information. We might as well have the computer do the work for us and simply calculate these values on the fly, when we need them. Rather than store them permanently in our data tables, where they can become outdated if special precautions aren't taken. Any time you want to start to store data in a field that includes the words total or average or minimum or maximum, or any number of additional modifier words, it might be a strong indication that you're attempting to store information that could be better calculated on the fly.
Resist the temptation to store these types of data and let the computer do what it was designed to do. This includes all manner of mathematical operations, like finding the total price when you know the quantity of a product ordered and its price per unit, as we saw here. Or determining the amount of tax owed given a total price in the tax rate. When you start exploring your specific relational database management system, you'll find a whole host of built-in mathematical computations and formulas that you can use to transform your data from one value to another or to derive information from a combination of multiple values.
One last point, the problem with storing calculated information doesn't just stop with numerical data. We can also apply these concepts to text data through text manipulations like character extraction, such as using the first letter of the middle name as the middle initial. Or something called concatenation, which is a fancy way of saying to join pieces of text together, like combining your First Name field and a Last Name field to obtain a full name. We can use concatenation to also return variations on this. Maybe we join the Last Name to a comma and a space and then join all of that to the First Name to arrive at list we can sort by Last Name.
You can see that there's a wide variety of ways we can manipulate our data in the database and by letting the computer do the work for us, we simplify our jobs and eliminate the possibility for conflicts to occur in our data. It's a win-win solution.
- What is a database management system (DBMS)?
- Moving through the database development cycle
- Preventing duplicate, inconsistent, and conflicting data entries
- Gathering requirements
- Developing relationships
- Identifying key fields
- Following a naming convention
- Developing the actual database