15.2.1- The Data Model

The Data Model

A data model consists of the tables that store the information, as well as the relationships that these tables have with each other. Let's look at the different data tables in our application and see what types of information are stored in each.

The Person Table

The Person table is used to store information about each user that is accessing the system. This information includes the user's name (their family name and their given name) and address, as well as their e-mail address. In our application, each individual user will be identified uniquely in the database via a unique numeric identification number, which is generated by Access and will be stored in the PersonID field. In order to log in to the system, users will identify themselves by giving their e-mail address and password – which are also stored in the Person table.

The structure of the Person table is:

The Seller Table

The Seller table is used to store additional information about any registered user who is a seller – that is any user who makes an item available for sale. This information is primarily statistical, detailing the numbers of items that the seller currently has for sale, as well as the total number of items that they have ever listed. This table is associated directly with the Person table – each user defined in the Person table can have either zero or one seller record, indicating whether or not they are a seller.

The structure of the Seller table is:

We noted above that there is an association between the Person and Seller tables. This association is defined using keys.

What is a Key?

When we're accessing the records of a database, we need to be able to do so with confidence that we're accessing exactly the right records. To help us do this, we use keys. A key is a field (or set of fields) that uniquely identifies a particular record in the table. For example, the PersonID field of the Person table is uniquely generated by the Person table (as we mentioned a moment ago), and so we can use it as a key for the Person table.

The way in which we set up the keys in our tables determines the uniqueness of records, and also allows us to link these records to records in other tables.

We can set up a relationship between two tables by using a foreign key. The idea is to include a foreign key as a field of one table – it gives us a way of referencing a unique record in another table. For example, the PersonID field of the Seller table refers us to the PersonID field of the Person table, which is a key and thus describes a unique individual. So the Seller table's PersonID is a foreign key for the Person table. This process links Seller records to the corresponding Person record. This process of having two seperate tables ensures that we don't have blank fields for people who only buy or browse the system.

The Item Table

Now that we have defined the users and the sellers in the application, we need a place to store the goods that are being sold. These are stored in the Item table. The Item table holds descriptive information about the item for sale, the pricing information, and some 'current status' information as well.

The structure of the Item table is:

The Bid Table

When a potential buyer finds an item that they'd like to buy, they can place a bid on that item. A bid is an intention to buy an item at a specified price. This bid price must be higher than any previous bid prices for the same item. Each new bid is saved to the Bid table. The bid history for each item is retained in the Bid table, so that the seller can review the interest in each item they're selling.

The structure of the Bid table is:

The Sale Table

The seller of an item is able to review the various bids that have been made on that item. When the seller finds an acceptable bid, they can accept that bid. This will begin the actual sale process. The first part of the sale process involves recording information about the winning bid in the Sale table, and notifying the user who placed the winning bid. The sale is considered 'pending' (see the ItemStatus field of the Item table above) until the successful bidder acknowledges the purchase of the item.

The structure of the Sale table is:

1.    The first step is to create the database, using Microsoft Access (we've used Access2000, but you can use Access97). First, start up Access from your Start menu. To create the database, select File | New from the toolbar and select the Database icon. You'll be presented with the following dialog:

Note that if you are using Access97 to create this database then the appearance of these dialogs will be slightly different.

You should also note that this database is available for download from the Wrox site  in both Access97 and 2000 versions

Choose a location for your database: In this example, we've chosen the directory E:\datastores. Type in the name of your database: here, we've called it Classified.mdb. Then hit the Create button. Once you've created the empty Classified.mdb database, Access should present you with a new Classified:Database dialog.

2.    Now that our empty database exists, we can start to build it up. We'll begin with the Person table. To create the new table, click on the New button on the Classified:Database dialog, and select Design View to create the new table. Then hit OK.

3.    Now, we can begin setting up the fields in our database. Add the fields and set their data types so that it looks like this (don't worry about the name of the table just yet – we'll set it in a moment):

We've only completed the Field Name and Data Type columns here – this information is compulsory for each field. Completing the Description column is optional, but it's useful if you want to add your own notes about the fields.

This will ensure that, for every record in this table, the PersonID field contains a unique value.

5.    The StreetAddress1, StreetAddress2, City, State, PostalCode and Country fields will be optional – that is, an end-user will be able to leave these fields blank when registering with our system. All the other fields in this table will need to be populated.

To arrange this configuration, we need to ensure that the six fields listed above will allow zero-length strings. To do this, select each of these fields in turn, then left-click on the Allow Zero Length field, and select Yes in the resulting dropdown menu.

6.    Finally, save the table by pressing the Save icon, , and naming the table Person when prompted to do so.

7.    Next, we'll create the Seller table. In order to create the Seller table – and indeed all the remaining tables – we essentially repeat the procedure outlined in steps 2 through 6. To recap, these steps are:

• Create a new table in Design Mode
• Add the fields and set their types
• Set the primary key
• Save the table

For the Seller table, add the fields shown here:

Set the ID field as the primary key, and save the table with the name Seller.

8.    Next, create the Item table. For the Item table, add the fields as shown here:

Set the ItemID field as the primary key and save the table as the Item table.

9.    Create the Bid table. For the Bid table, add the fields as shown here:

Set the BidID field as the primary key, and save the table using the name Bid.

10.  While setting up the Bid table, highlight the Timestamp field; then, in the Default Value box, insert Now():

11.  Finally, create the Sale table, with fields as shown here:

Set the SaleID field as the primary key, and save the table as the Sale table.

How It Works

Here, we've created the Classified.mdb relational database containing the five tables that we'll need to store all the information for our classified ads application. We have created primary keys for each table: as we explained earlier, these keys will help us to retain consistency within each of the tables. The primary keys also allow us to build up relationships between the tables, which mimic our data model.

Notice that the data type of the primary key of each table is AutoNumber. AutoNumber is a special data type. When a new record is added to a database, any field of type AutoNumber is automatically populated with a number that is generated by the database at the time. That means that we won't have to write script to generate these numbers; moreover, the database will prevent us from changing the values in these fields.

Note that the New Values option for of each of these primary fields is Increment (you can see this in the screenshots above). This indicates that the system will generate new values for the field incrementally. Moreover, such a field will contain a unique value for each record in the table – thus we easily generate a unique primary key for the table.