Questions on Auction Database Schema

Question

I was looking at the following db model and I had some questions on it. I'm sure it's a good design as the guy behind it seems to be reasonably well qualified, although some things don't make sense:

1. Why's he seperated out bidders and sellers? I thought you'd have users, and users can place bids and sell items. You'd have a bids table with a reference to user, and a auctions table, with reference to user table. He talks a lot in his tutorials about making sure models are scalable and ready for change (don't have a status column for instance, have statuses in another table and reference that) so what's up here?
2. Why are their fields like "planned close date" and "winner". Isn't this data duplication, as the planned close date could be calculated using the last bid time (for acutions that use auto extend) and the winner is simply the last bid when the auction closes..?

FYI: I'm trying to build my own auction site in PHP/MySQL from scratch and it's proving to be quite difficult, so tutorials on this would be great!

Thanks!

Answer 1

Concerning "planned close date" and "winner":
Yes, it is data duplication, but in some cases you have to live with that in order to scale properly.

Of course you can use the last bid time from the "Bids" table to calculate the close date of the auction, but if your site gets really big, you don't want to calculate this every time someone loads the "auctions ending soon" list - because you have to calculate it for every single active auction, every time, just to find the few ones that are ending soon.
(and this list will get loaded a lot, believe me!).

Same with the winner - it's just faster to load if you have the information in the auctions table, so you don't always have to join the "Bids" table and get the user from the last bid of every auction.
Think of the page in "My eBay" which shows all the auctions you won in the last 60 days - you would have to search all the bids of all auctions for the winner every single time someone loads this list!

A perfectly normalized database isn't always the best solution if you expect it to scale with lots of users.

Answer 2

Why's he seperated out bidders and sellers?

Each table has unique columns specific to each one, so he keeps them separate. I would actually go with user and sub-type bidder and seller to the user, like:

TABLE User (UserID (PK), ... all common fields for any user)
TABLE Bidder (UserID (PK,FK) ... all fields specific to bidders)
TABLE Seller (UserID (PK,FK) ... all fields specific to sellers)