Datasheet

Figure 1.6
As you can see, the foreign key, account_id, is used in the mapping just as it is in the one-to-one rela-
tionship map. When an account is added to the database, its ID is obtained and used to store one or
more addresses in the address table. The ID is stored in the account_id foreign key column. The appli-
cation can perform a query against the address table using the ID as a search value.
Mapping a Many-to-Many Relationship
In the final type of relationship, an account relates to securities and at the same time securities relate to
accounts. Figure 1.7 shows the classes in the relationship.
As you might expect, doing the mapping for a many-to-many relationship isn’t as easy as it is for the
one-to-one and one-to-many cases. For every account and security in the system, we need to be able to
relate one or more of the other classes. For example, if we have an account with an ID of 3425, it might
be associated with the securities 4355, 3245, 3950, and 3954. How can we associate these securities with
the single account? We could have four columns in the account table called sec1, sec2, sec3, and sec4. But
what if we added two more securities to the account? We wouldn’t have enough columns in the account
table.
The solution is to use an association table between the account table and the security table. Here are the
table schemas:
create table account (
ID int not null primary key auto_increment,
type int,
opendate date,
securities int
);
create table security (
ID int not null primary key auto_increment,
name varchar(256),
address
ID : int
address : String
city : String
state : String
zip : String
account_id : int
account
ID : int
type : int
opendate : date
owner : Owner
Addresses : ArrayList
13
Introduction to Mapping Objects to Relational Databases
03_576771_c01.qxd 9/1/04 12:09 PM Page 13