Data Modeling

One thing about building Web sites is that you hit upon a lot of different disciplines. Fortunately, you don't have to be an expert at all of them. I bring that up now because data modeling is another concept that could have a book of its own. In designing a database, there are a lot of factors to consider, from deciding what database to use in the first place, to how to lay out the files on the disks, to what tables to use. In this case, however, you have a very small database, so you can discount all but the tables themselves.

Normally, you would design the entire database first, so that if one section affects the others, you don't have to make changes later. To keep things simple in this project, however, you design each section as it's needed.

Determining Entities

An entity is something of importance that needs to be tracked. In this case, you track users, but exactly what information about the users are you tracking? If you put everything in one table and laid it out spreadsheet style, it would look like Figure 4.13.

You certainly could track that way, but what if you add another preference? You'd have to add another column to the table. Plus, you'd have all that wasted space keeping track of preferences that don't apply to the user in question. Instead, consider the user and their preferences to be two different entities, related to each other (see Figure 4.14).

You could eliminate the wasted empty space and the problem of adding extra columns by listing it, as shown in Figure 4.15.

But then you waste even more space by listing the user's information over and over, when all you really need to know is which users owns each preference. We can do that with just two tables (see Figure 4.16).

In this case, use the username as the key that links the information together.

Two words about naming things: be consistent. It doesn't really matter what your naming convention is, as long as you stick to it. It would be nice, though, if other people could understand what you were doing based on how you named things. That's why I named the second table user_medium. One glance and I know not only what it is, but also what else it's related to.

The Actual Tables

Now that you know what you're recording and how you're recording it, you need to finish designing the tables. You know what columns to use in each table; now you need to decide what kind of data goes in them and how much of it. The create scripts for these two tables would be as follows:

create table members (
    username    char(30),
    password    char(30),
    first_name    char(30),
    last_name    char(30),
    email     char(30),
    comments    char(500),
    believes_in_aliens    char(30)
    )

create table user_medium (
    username     char(30),
    medium    char(30)
    )

A create script allows you to create a database object such as a table without having to go through any special tools. They can be run anywhere that you can run SQL, and enable you to easily recreate your tables if necessary.

Assume that all your columns are going to hold text, and most of them are going to be 30 characters long; that's larger than you really need, but that's ok for now.

In your database, create two tables the same way you created the ad_log table. If you don't have a database installed in your system and you created an Access database when we set up the DSN, you can use the execute command to run the create scripts and create your tables.

Inserting the data

Now that you have the tables, you can insert your member data. You can do this exactly the same way that you did for the ad logs using the code in Listing 4.14.

Code Listing 4.14. take_registration.asp: Inserting data

0: <%@ LANGUAGE="VBSCRIPT" %>
1: <!--#include virtual="/pagetop.txt"-->
2: <%
3:     p_userid = Request.querystring("p_name")
4:     p_pass1 = Request.querystring ("p_pass1")
5:     p_pass2 = Request.querystring ("p_pass2")
6:     p_first = Request.querystring ("p_first")
7:     p_last = Request.querystring ("p_last")
8:     p_email = Request.querystring("p_email")
9:
10:    set outpostDB = Server.CreateOjbect("ADODB.Connection")
11:
12:    theSQL = "insert into members "
13:    theSQL = theSQL & "(username, password, first_name, last_name, "
14:    theSQL = theSQL & "email, believes_in_aliens)"
15:    theSQL = theSQL & " values ('"&p_userid&"', '"&p_pass1&"', '"
16:    theSQL = theSQL & p_first&"', '"&p_last &"','"&p_email&"', '"
17:    theSQL = theSQL & p_believes_in_aliens&"')"
18:
19:    outpostDB.Execute(theSQL)
20:
21:    for each p_medium in Request.querystring("p_medium")
22:        theSQL = "insert into userid_medium (userid, medium) values
						('"&p_userid&"',
 '"&p_medium&"')"
23:        outpostDB.Execute(theSQL)
24:    next
25:
26:    outpostDB.close
27:    set outpostDB = Nothing
28:
29:%>
30:
31:<H2>User Registration</H2>

…

Now fill out a membership form and submit it, and check your tables. You're done with the registration form, right? Wrong. If you reload the page, you get another copy of your membership information in the tables, but you don't want more than one person to have the same username. You can handle this in two ways. One way is to simply check for duplicates before you do the insert, but that's an extra step to slow you down.

The other way to handle it is to designate the username column as a primary key in the database.

Set the username column in the members table as a primary key. If you're in Access, click the column and at the bottom of the column, where it says Indexed, choose Yes (No duplicates). Beside Required, choose Yes. (That's all a primary key really means: that it's unique and it's not empty.)

After you do that, the database won't let you insert duplicate records. All you have to do is try the insert and check for an error.