Database Reference
In-Depth Information
straint just as easily with a validation
view
as you can with a validation
table.
The dif-
ference between the two lies in their construction—a validation
table
stores its own data,
whereasavalidation
view
drawsdatafromitsbasetables.Althoughyoucandefineavalid-
ation view using one or more base tables, you'll commonly define a validation table using
a single base table and incorporate only two or three of the base table's fields. (This struc-
ture is quite similar to that of a validation table.)
For example, let's say you're designing a database for a small contractor and you're work-
ing with the tables in
Figure 12.10
.
Figure 12.10. Tables from a database for a small contractor
As you can see, the S
UBCONTRACTOR
ID field in the SUBCONTRACTORS table provides
the range of values for the S
UBCONTRACTOR
ID field in the PROJECT
SUBCONTRACTORS table. (Recall that a foreign key draws its values from the primary
key to which it refers.) You've determined, however, that you want to restrict the access
users currently have to certain fields in the SUBCONTRACTORS table; you've decided
that the only fields users should be able to access are the S
UBCONTRACTOR
ID, SCN
AME
,
SCP
HONE
N
UMBER
, and SCE
MAIL
fields. So, you define a validation view called
APPROVED SUBCONTRACTORS that will incorporate these fields and still provide the
range of values for the S
UBCONTRACTOR
ID field in the PROJECT SUBCONTRACTORS
table.
Figure 12.11
shows a revised diagram of the tables, including the new view.