2
Data design
Databases manage data in “tables.” The table structure in databases is
shown below.
Column (field)
Customer table
Item name (field name)
Customer code
Customer name
Phone number
Location
A-1
B-1
A-20
Nanboku Denki
Nihon Kogyo
Iroha Denshi
03-3592-123X
06-6967-123X
078-927-123X
Tokyo
Osaka
Hyogo
Row (record)
Primary key: Identifies each record in the table
For example, if the customer code is specified, the corresponding
customer name can be identified.
If managing multiple tables, a “relationship” should be considered. A relation refers to linking two tables according to “primary keys” and “foreign keys.”
A foreign key is used when searching for data in a different table.
For example, in the following two tables (order table and customer table),
the order table does not contain the customer name. However, by linking
the two tables using the customer code, the customer name can be obtained
from the customer table based on the value for the customer code in the
order table.
In this case, the customer code in the customer table is called the primary
key, and the customer code in the order table is called the foreign key.
“Referential constraints” are used to prevent inconsistencies when setting foreign keys. For example, only a value that exists in a reference (primary key table) field can be entered in a field for which an external key
has been set.
Order table “Primary key” of order table
Order No.
0001
0002
0003
0004
Order date
2008.10.02
2008.10.02
2008.10.02
2008.10.03
Customer table
“Foreign key” corresponding to customer table
Customer code
Product name
Volume
W-type radio
30
A-1
X-type monitor
20
B-1
Y-type radio and cassette player
100
B-1
Z-type radio and cassette player
5
A-20
Items in the customer table can be referenced based on customer code
Customer code Customer name
Nanboku Denki
A-1
Nihon Kogyo
B-1
Iroha Denshi
A-20
Phone number
03-3592-123X
06-6967-123X
078-927-123X
Location
Tokyo
Osaka
Hyogo
“Primary key” of customer table
If a referential constraint has been set, records in the customer table corresponding to
customer codes (B-1, for example) in the order table cannot be deleted, and the customer
code itself cannot be overwritten. Also, records containing customer codes not found in the
customer table cannot be added to the order table.
251