Table definitions

By and large, all of the ID values are tied to a auto-incremented number defined for each table. This insures nice unique values.


Auctions

ViewTime is the time that people may begin viewing the auction items. StartTime is the time the auction actually begins. Location is the index number of the auction locations since multiple auctioneers might be using the same auction house at different times.

create table Auctions
(
    ID          int auto_increment primary key,
    Name        char(32),
    ViewTime    datetime,
    StartTime   datetime,
    BuyersFee   char(10),
    EnterFee    char(10),
    Auctioneer  int,
    Location    int
);


Locations

The Phone and Fax numbers are very optional, in case this is a rented out place that folks can call to get directions or something like that. The Notes section is for generic notes about the place, like if it has 3 arenas or something like that. Directions are just that, directions on how to find the place. LastRef is the last time this location was referenced by an auction. It's a means to clean up the database at some later time.

create table Locations
(
    ID          int auto_increment primary key,
    Name        char(32),
    Addr1       char(32),
    Addr2       char(32),
    Addr3       char(32),
    City        char(32),
    State       char(2),
    ZipCode     char(5),
    Phone       char(24),
    Fax         char(24),
    Notes       text,
    Directions  text,
    LastRef     date
);


Auctioneers

create table Auctioneers
(
    ID          int auto_increment primary key,
    Name        char(48),
    Addr1       char(32),
    Addr2       char(32),
    Addr3       char(32),
    City        char(32),
    State       char(2),
    ZipCode     char(5),
    Phone1      char(24),
    Phone2      char(24),
    Fax1        char(24),
    Fax2        char(24),
    Email       char(64),
    WebSite     char(64),
    Notes       text,
    LastRef     date
);


Catagories

The level of the record is how many levels deep it is within the hierarchy of catagories. For example, Antiques would be a top level catagory and have a level of 1. Antiques:Cars would be on the second level and have a level of 2. Antiques:Cars:Steamers would be a third level, etc. The level is provided to allow easier sorting later on. NOTE! The database is currently limited to 4 levels. Mostly because the names are concatinated together and really long titles will cause the tables to appear awkward. If you need more than four levels, I'd suggest you look at restructing your catatories first.

HigherCat is a return pointer to the catatory this record is part of. For example, Antiques:Cars would have HigherCat pointing to Antiques.

create table Catagories
(
    ID          int auto_increment primary key,
    Name        char(32),
    Level       int,
    HigherCat   int
);


CatagoryList

This table is a copy of the Catagories table and used to speed access when a list of all the catagories is needed. For example, if you have the catagories Antiques and Antiques:Car, this table will have two entries with Name set to "Antiques" and "Antiques:Car" while the Catatories table will have two entries with names of "Antiques" and "Car". Creating the list from the Catagories table would be more difficult. 'Num' is the number of auctions which currently list items in that catagory. Incrementing multiple 'Num' variables on insert takes more SQL code but is much faster than trying to figure this out on the fly.

create table CatagoryList
(
    ID          int,        # Identical to Catagories.ID
    Name        tinytext,
    Num         int
);


Auction_Catagory_Xref

This table does the cross-reference between the Catagories and the Auctions which are selling this kind of items.

create table Auction_Catagory_Xref
(
    AuctID      int,
    CatID       int
);


States

A listing of the 50 states and their two letter abreviations along with the count of the number of auctions which are listed within that state.

create table States
(
    Abrev       char(2),
    Name        char(20),
    Num         int
);


ZipCode

A listing of the 5 digit zipcodes in the US and the lat & longitude for each of them. This allows us to calculate the direct line distance between any two points. While not the most accurate of methods, (roads wander around), it gives users an idea of how far away the auction is.

Note, Longitiude is 'Lng' because 'Long' is a reserved name in MySQL.

create table ZipCode
(
    ZipCode     char(5) NOT NULL,
    Lat         float(4,6),
    Lng         float(4,6),

    UNIQUE idx1 (ZipCode)
);


Home | Contact Us | Administer Site


© 1998, Site Designed by Vt. Web Wizard