Datasheet

To map the relationship shown in Figure 1.5 to a relational database, we need to use a foreign key that
associates the owner with the account. Since there will be a single owner for each account, we know
there will be a single row in the database table that holds the owner information. The following table
schemas can be used to map the relationship in Figure 1.5:
create table account (
ID int not null primary key auto_increment,
type int,
opendate
);
create table owner (
ID int not null primary key auto_increment,
account_id int,
name varchar(256),
birthdate date
);
The one-to-one mapping is created between the account and owner tables through the account_id field
in the owner table. When a new row is added to the account table, a new ID is created. This ID uniquely
identifies the account to the accounting system. No matter how many accounts are added to the system,
no other one will have the same ID value. Therefore, this value can be used to associate the account and
its database row with any other necessary data. Some of that data is found in the owner table. Just after
the account is added to the database, the owner row is inserted. Part of the owner information to be
stored is the ID of the account. The ID is stored in the account_id field, which is a foreign key to the pri-
mary key (ID) of the account table.
When the application needs to pull the account, it accesses the account through the primary key or,
potentially, through a query that pulls the ID as well. The application can then pull the owner row using
the ID value.
Mapping a One-to-Many Relationship
In the previous example, we also have a one-to-many relationship when the account object relates to one
or more addresses. Figure 1.6 shows an example of the objects we need to map.
A single account in our application can have one or more addresses. Thus, each account needs an array
of address objects that hold the full address where information can be sent and associated with the
account. The following schemas show how the information is kept in the database:
create table account (
ID int not null primary key auto_increment,
type int,
opendate date
);
create table address (
ID int not null primary key auto_increment,
account_id int,
address varchar(256),
city varchar(256),
state varchar(256),
zip varchar(256)
);
12
Chapter 1
03_576771_c01.qxd 9/1/04 12:09 PM Page 12