Database design question

megakid · 16 Feb 2007 at 00:49

I am trying to implement an online auction program - very similar to eBay.
At the moment I have tables including Users, Auctions (linked Auctions.SellerID -> Users.UserID), Bids (Bids.Bidder -> Auctions.UserID, Bids.AuctionID -> Auctions.AuctionID)...you get the idea.

The issue I'm facing now is the feedback system... I want one similar to ebay where for each auction, once finished, the seller gets to leave feedback for the buyer and vice versa. So here's what I've thought of so far:

Feedback table, with AuctionID, BuyerID, SellerID and a flag indicating if its a Buyer-to-Seller feedback (ie the Seller gets the feedback rating) or Seller-to-Buyer.
Is this normalised? In theory I could get the highest bidder (ie the Buyer) from the Bids table and the seller from the Auctions table.

OR

Sales table with AuctionID, BuyerID, SellerID - an entry in here means that the Auction is complete and is now a "Sale". From here I have a Feedback table linking to the correct Sale (ie 2 rows in the Feedback table per Sale row if both buyer and seller leaves feedback)....
Is this normalised??!

Basically I havent really got any decent ideas on how to create this in a normalised way.

Can someone help?

rpstewart · 16 Feb 2007 at 08:41

Unless this is an academic exercise then treat normalisation with a pinch of salt. Yes it's great to only store each data element once and remove all the wasted storage but there are times when over normalising hurts performance - storage is cheap, processing power less so.

I would be tempted to do the feedback as a name-value pair table. That way you have the ability to use it as a general "auction comments" table which could contain more than just feedback although it has the potential to be less performant than a dedicated feedback table.

The table would end up as:

AuctionID - links to auction table
Comment_Type - Text or numeric indicator of record type (1=buyer feedback etc)
Comment_Value - Text string containing feedback comments etc

You only insert rows when you have data so you can easily determine which auctions don't have any feedback simply by looking for auction IDs with no records of a particular type.

When you go to retrieve the details for the auction as a whole all you do is pull back all the comments by auction ID and format the results accordingly. Unfortunately this approach can get a bit messy if you want a single row per auction report but it works well for a website where pages can be built using multiple queries.

purejamie · 16 Feb 2007 at 09:10

rpstewart said:
Unless this is an academic exercise then treat normalisation with a pinch of salt. Yes it's great to only store each data element once and remove all the wasted storage but there are times when over normalising hurts performance - storage is cheap, processing power less so.

Agreed, sometimes best to de-normalise slightly to increase performance (when you have many many joins going on).....

megakid · 16 Feb 2007 at 18:29

Thanks for your help rpstewart, I've gone for a similar method to what you described.

A separate issue I am now facing is how to find the highest bidder of an auction:

Code:

SELECT MAX(b.Bid) As Price, b.BidderUserID, b.AuctionID 
FROM Bids b GROUP BY b.AuctionID

Returns an error - so I remove b.BidderUserID and it works (and i do understand the error) but my question is, is there anyway I can run this query, returning the a list of all auctions along with their highest bidder?

Cheers!

rpstewart · 16 Feb 2007 at 18:32

You'll need to group by BidderUserID and AuctionID

megakid · 16 Feb 2007 at 18:39

Wow, quick reply - impressive! Thanks.

Yes, that cures the problem but doesnt give me my required result -

I want to list distinct AuctionIDs, linked to the current highest bidder.

If I do as suggested, I will get the maximum bid EACH user has put forward towards each auction.

Maybe I am a little too expectant of MSSQL but I thought it was possible to link the row returned to the BidderUserID without grouping it into the aggregate function.

rpstewart · 16 Feb 2007 at 18:52

megakid said:
Yes, that cures the problem but doesnt give me my required result -

I want to list distinct AuctionIDs, linked to the current highest bidder.

Very true, didn't think of that.

It should be do-able, gimme a few mins to ponder.

rpstewart · 16 Feb 2007 at 19:05

OK, now my MSSQL is rustier than a 20 year old Alfa Romeo but this works for DB2 so will probably be OK for MSSQL.

Code:

select
     Bids.AuctionID
     ,Bids.BidderUserID
     ,Bids.Bid
FROM
    Bids Bids
    ,(SELECT MAX(b.Bid) As Price
      , b.AuctionID 
     FROM Bids b 
     WHERE AuctionID=?
     GROUP BY b.AuctionID) Max_Bids
WHERE
    Bids.AuctionID=Max_Bids.AuctionID
    and Bids.Bid=Max_Bids.Price

It's possibly not the most efficient piece of SQL but it should work. The nested table finds the winning bid for the auction in question (or all auctions if you remove the inner where clause) then joins to the Bids table on AuctionID and the bid value to determine who placed the bid.

Beansprout · 16 Feb 2007 at 21:34

rpstewart said:
Unless this is an academic exercise then treat normalisation with a pinch of salt.

Aye - luckily we have a sensible lecturer who incorporated that into his lectures, so (iirc) nobody went further than 3NF or BCNF in our recent assignment, eventhough you could've probably gone further and become incredibly esoteric o.o

6NF for the win!