By and large, all of the ID values are tied to a auto-incremented number defined for each table. This insures nice unique values.
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
);
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
);
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
);
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
);
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
);
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
);
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
);
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)
);