Database Reference
In-Depth Information
You can see that this rule imposes a constraint on the S
UPP
S
TATE
field's range of values,
limiting them to AK, AZ, CA, CO, HI, ID, MT, NM, NV, OR, UT, WA, and WY. (Ac-
cording to the rule, you can't use a supplier based in some other state.) The easiest and
most efficient way to establish this rule is to store these values in a validation table called
STATES, and then to use the validation table as the source of the S
UPP
S
TATE
field's range
of values.
Consider the tables in
Figure 11.14
. (Note the new symbol that is used to represent a val-
idation table.) The SUPPLIERS table stores all the requisite data on the SUPPLIERS en-
gaged by the organization, and the STATES table is a new validation table that will store
the names and abbreviations of the specified states.
Figure 11.14. The SUPPLIERS table and the STATES validation table
Your first order of business (no pun intended) is to establish a relationship between these
tables. As you can see, there is a one-to-many relationship between them—a single record
in STATES can be associated with one or more records in SUPPLIERS, but a single re-
cordinSUPPLIERSwillbeassociatedwithonly
one
recordinSTATES.Youalreadyknow
thatyouestablishaone-to-manyrelationshipbytakingacopyoftheparenttable'sprimary
key and incorporating it within the structure of the child table where it becomes a foreign
key. Although the SUPPLIERS table already has a field named S
UPP
S
TATE
, you'll replace
it with the S
TATE
field from the STATES validation table. (This is a reasonable modifica-
tion because it is in accordance with the Elements of the Ideal Field and is consistent with
themannerinwhichyouestablishone-to-manyrelationships.)
Figure11.15
showsthenew
relationship diagram for these two tables.
