Posted by Schraalhans Keukenmeester on 09/28/05 10:41

s1037989@gmail.com wrote:
> Thanks for the reply. No, there is no money at stake. :) Actually, I
> am developing a web-based application for my Rotary International Club
> (www.washingtonrotary.com). I am indeed striving for a neat-looking
> result. One that will be easy to main by me, and others in the future.
>
> This application is for presenting the results of an FM radio-based
> auction live on the internet. People can find out what is currently
> being auctioned by listening to the radio and/or following along on the
> internet.
>
> The public will have no ability to place bids via the internet. Again,
> it is a totally radio-based auction. The web is just there to assist
> the listeners with what they are hearing on the radio. There is a team
> of admins powering the backend, entering in all bids as they come in.
> Also managing which items are up for auction when and which auctioneer
> will auction each item.
>
> Here is the guts of the database schema involved:
>
> Table: auctions
> Fields: auction_id, item_id, donor_id, auctiondate, auctioneer, status
> Table: items
> Fields: item_id, item_name, item_value
> Table: bids
> Fields: bid_id, bidder_id, auction_id, bid_amount
> Table: bidders
> Fields: bidder_id, (bidder contact info fields: name, address,
> phone,...)
> Table: donors
> Fields: donor_id, (donor contact info fields: name, address, phone,...)
>
> The 'auctions' table and 'items' table could potentially be merged, but
> the reason that I split them is that many items are donated SEVERAL
> times, and it seems it would make more sense to just enter the item
> once into 'items', and auction it SEVERAL times via 'auctions'. So,
> the 'auctions' table is NOT a single auction with several items;
> instead, it is a collection of auctions where each record in the table
> is its own auction, and also its own item.
>
> The 'bids' table is the history of ALL bids placed. It links an item
> from 'auctions' to a bidder from 'bidders'.
>
> An example of many similar but still different SQL queries is that I
> need to return from the database: all items, all sold items, all closed
> items, all queued items, all sold items within the past X seconds, all
> items on a certain date, all bids on an item, the highest bid on an
> item, ... And a large varying mixture of all the above.
>
> Here is an example of an SQL query I have. The only thing that ever
> really changes is the WHERE clause, but, since I am doing many
> sub-selects, there are WHERE clauses all over the place so it's
> difficult to determine via a function where a WHERE clause statement
> should go. Here it is:
>
> # Get max bid, item/bidder/donor information on a particular item
> SELECT *, auction_auctions.auction_id AS auction_id,
> auction_auctions.auctionnumber AS auctionnumber
> FROM
> (
> SELECT *
> FROM auction_auctions
> LEFT JOIN
> (
> SELECT
> *
> FROM
> (
> SELECT
> *,
> TIMEDIFF(bid_time, NOW()) AS
> timeago
> FROM auction_bids
> WHERE auction_id='%s'
> ORDER BY bid_amount DESC
> LIMIT 1
> ) AS auction_maxbid
> JOIN auction_bidders USING (bidder_id)
> ) AS auction_maxbidder USING (auction_id)
> WHERE auction_auctions.auction_id='%s'
> ) AS auction_auctions
> JOIN (auction_items, auction_donors)
> ON
> (
> auction_items.item_id=auction_auctions.item_id AND
> auction_donors.donor_id=auction_auctions.donor_id
> )
> ;
>
I'll have a closer look later today. Sofar I can imagine loading all in
PHP tables may result in HUGE tables consuming lots of memory.
I'll be back!
SH

[Back to original message]