Building a Membership Web Site - Java Database Programming Bible - page 308

For the purposes of this application, login with a valid user name and a bad password results in a
prompt for an e-mail reminder, and completely erroneous logins lead the user along a registration trail.
Although this is a simplistic approach, it serves to illustrate the technology and provides a starting point
for a more complete solution.

The Login Table itself is very simple. It uses only the three columns illustrated in Table 11-1 . The
UserName column is the primary key and, as such, is indexed for speed of access. The price of fast
access for returning users is that inserting new users is slower because of the need to build the index.
Table 11-1: Login Table

UserName Password MemberID

axman hatchet 7

batman robin 3

cat balou 8

garfield lasagna 1

snoopy peanuts 2

The Password column is used simply for user validation, as shown in the examples in Chapter 12 . The
MemberID column, however, is the key to accessing all the other tables.

The importance of the MemberID field lies in the fact that it is the unique identifier used to access
member-specific data from all the other tables. In most of the tables, MemberID is also the primary key,
since each member has only one entry in most of the tables.

Some of the tables, however, have their own primary keys and use MemberID as a foreign key. For
example, all member photos are stored as binary large objects (blobs) in the Photos Table. This table
contains a unique PhotoID, which is the primary key, and the MemberID, which is a foreign key used to
associate the photo with a specific member. The Photos Table also contains a column for the photos
themselves, as well as a descriptor column used for selecting individual photos.

Member Registration

When a new member goes into the registration process, the system displays an HTML form for the
member to complete. The data from the form is then saved to the Contact_Info table, shown in Table
11-2 .
Table 11-2: Contact_Info Table

I
D

FNAM
E

M
I

LNAME STREE

T

CIT
Y

S
T

ZIP PHON

E

EMAIL

1 Vito A Corleon

e

123
Main St

New
York

N
Y

1000
2

212-
555-
0000

[e-mail]
m

2 Fred A Tagliatell

e

123
Main

Phil
a.

P
A

1234
5

123-
456-
7890

[e-mail]

7 Al X Edwards 123

Pine

New
York

N
Y

1234
5

123-
456-
1111

[e-mail]
m

The Contact_Info Table is the only place in the database where the name and address information of
the members is stored. The primary use of the Contact_Info table is for billing and administrative
purposes.