| Lesson 5 | Constraints and keys |
| Objective | Describe common mistakes associated with constraints and keys. |
Database key Constraints
Mistakes with constraints
The most common constraints involve limits you place on data that can be entered in a field. Creating constraints gives rise to many of the same mistakes associated with their running mates, business rules. You can err by mistyping a constraint when you create it, which inadvertently creates contradictory constraints.For example, if you want to ensure the total cost of an order is at least $1.00 and less than $500, you can create the following constraint:
OrderCost > 1 AND OrderCost < 500 Suppose, however, you mistype the constraint as follows:
OrderCost < 1 AND OrderCost < 500 The RDBMS will only accept orders that cost less than $1.00, violating the business rule upon which the constraint is based.
Note: If you're having trouble with a constraint, check the constraint carefully to make sure it reflects the organization's business rule and that it was entered correctly.
Not Planning for Change
As you design the database, look for places that might need to change in the future.
You do not need to build features that may never be needed, but you do not want to narrow the design so those features cannot be implemented later. In particular, look for exceptions in the data. Customers often think in terms of paper forms, and those are easy to modify.
It is easy to cross out headings and scribble in the margins of a paper form and it is a lot harder to do that in a computerized system.
Whenever you see two or more things that have a lot in common, ask the customers if those are enough or whether you’ll sometimes need to add more. Listen for words such as "except", "‘sometimes". Those words often hint at changes yet to come.
For example, suppose a customer says, ‘‘This field holds the renter’s front binding tension. Here the word ‘‘unless’’ tells you that this one field may not be good enough to hold all of the data.
For another example, suppose the customer says, "The order form must hold two addresses, one for shipping and one for billing. Unless, of course, we’re billing a split order."
This says that two address fields (or groups of fields) isn’t enough. At this point you probably need to pull the address data into a new table so you can accommodate any number of addresses, including the ones the customer has not remembered yet.
For a third example, suppose you are building a coaching tool for youth soccer teams. Figure 10-1 shows your initial design.
Whenever you see two or more things that have a lot in common, ask the customers if those are enough or whether you’ll sometimes need to add more. Listen for words such as "except", "‘sometimes". Those words often hint at changes yet to come.
For example, suppose a customer says, ‘‘This field holds the renter’s front binding tension. Here the word ‘‘unless’’ tells you that this one field may not be good enough to hold all of the data.
For another example, suppose the customer says, "The order form must hold two addresses, one for shipping and one for billing. Unless, of course, we’re billing a split order."
This says that two address fields (or groups of fields) isn’t enough. At this point you probably need to pull the address data into a new table so you can accommodate any number of addresses, including the ones the customer has not remembered yet.
For a third example, suppose you are building a coaching tool for youth soccer teams. Figure 10-1 shows your initial design.
The next lesson describes mistakes associated with primary and foreign keys.