Auction Database Tables

The first thing we need to do, now that we have some requirements, is to figure out how to design the database. Our auction database consists of four tables-auction, donor, item, and bids. This is about as normalized as we'll need to get for this small application.

The auction table looks like this:

+---------------+-------------+------+-----+---------+----------------+ | Field         | Type        | Null | Key | Default | Extra          | +---------------+-------------+------+-----+---------+----------------+ | auction_id    | int(11)     |      | PRI | NULL    | auto_increment | | name          | varchar(50) | YES  |     | NULL    |                | | start_bidding | datetime    | YES  |     | NULL    |                | | stop_bidding  | datetime    | YES  |     | NULL    |                | +---------------+-------------+------+-----+---------+----------------+

First, we have the auction_id, which is our primary key. This field is also set to auto_increment; each time we enter a record, the database automatically handles updating this field for us.

Next is the name field; this is the name of the auction. It is currently set to 50 characters, but if you have auctions with longer names, feel free to change this value.

start_bidding is a datetime field that contains the date and time when bidding for this auction can begin.

stop_bidding is also a datetime field. This contains the date and time when bidding ends.

Take a look at the donor table.

+----------+-------------+------+-----+---------+----------------+ | Field    | Type        | Null | Key | Default | Extra          | +----------+-------------+------+-----+---------+----------------+ | donor_id | int(11)     |      | PRI | NULL    | auto_increment | | name     | varchar(50) | YES  |     | NULL    |                | | address1 | varchar(50) | YES  |     | NULL    |                | | address2 | varchar(50) | YES  |     | NULL    |                | | city     | varchar(50) | YES  |     | NULL    |                | | state    | char(2)     | YES  |     | NULL    |                | | zip      | varchar(10) | YES  |     | NULL    |                | | phone    | varchar(20) | YES  |     | NULL    |                | | contact  | varchar(50) | YES  |     | NULL    |                | +----------+-------------+------+-----+---------+----------------+

The donor table is used to maintain the information about who has donated the items. Since a donor can donate many items, this table makes it so that we store as little redundant data as possible. When we enter the auction items, instead of having to enter a bunch of donor data for each item, we simply store the donor_id. That links the auction item to the proper donor.

The donor_id for this table is the primary key. This is also an auto_increment field, so we really don't have to do anything with it when we add data to the table. The other fields in this table should be self-explanatory. They are simply text fields that hold information about the donor.

The next table is item. This table stores the actual items that will be bid upon.

+-------------+--------------+------+-----+---------+----------------+ | Field       | Type         | Null | Key | Default | Extra          | +-------------+--------------+------+-----+---------+----------------+ | item_id     | int(11)      |      | PRI | NULL    | auto_increment | | name        | varchar(50)  | YES  |     | NULL    |                | | description | varchar(255) | YES  |     | NULL    |                | | image_url   | varchar(200) | YES  |     | NULL    |                | | donor_id    | int(11)      | YES  |     | NULL    |                | | value       | double       | YES  |     | NULL    |                | | min_bid     | double       | YES  |     | NULL    |                | | min_incr    | double       | YES  |     | NULL    |                | | auction_id  | int(11)      | YES  |     | NULL    |                | +-------------+--------------+------+-----+---------+----------------+

This table also has an auto_increment field as its primary key. We have several text fields that store information about the item. Three fields are of type double; these fields contain the value of the item, the min_bid (minimum starting bid), and the min_incr (minimum bid increment).

Notice the other two int(11) (integer) fields in this table: named donor_id and auction_id. These two fields contain the donor_id and the auction_id that pertain to this item. These are called foreign keys. Foreign keys are fields in one table that link to the primary keys of another table. By using these foreign keys, we are able to tell exactly which donor has given this item and which auction this item is in.

Finally, we have the bids table. This table keeps track of the bids and maintains a record of all bidding activity.

+------------+---------------+------+-----+---------+----------------+ | Field      | Type          | Null | Key | Default | Extra          | +------------+---------------+------+-----+---------+----------------+ | bid_id     | int(11)       |      | PRI | NULL    | auto_increment | | item_id    | int(11)       | YES  |     | NULL    |                | | amount     | double        | YES  |     | NULL    |                | | bidtime    | timestamp(14) | YES  |     | NULL    |                | | first_name | varchar(40)   | YES  |     | NULL    |                | | last_name  | varchar(40)   | YES  |     | NULL    |                | | phone      | varchar(20)   | YES  |     | NULL    |                | +------------+---------------+------+-----+---------+----------------+ 

The bids table stores the first name, the last name, the phone number of the person bidding, and the bid amount. This table also includes an auto_increment field as its primary key and links to the item being bid upon via a foreign key. The one different field type you'll see here is the one for the bidtime field. This field is of the timestamp type. This is another field that automatically gets handled by the database when adding records. This field stores the time and date when the record was added.

We'll be diving into the CGI code soon. Once we are through the code for generating the pages, we will look at the module that has some additional subroutines we use for our auction site.



Perl Database Programming
Perl Database Programming
ISBN: 0764549561
EAN: 2147483647
Year: 2001
Pages: 175

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net