Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past.

Ok, my implementation needs to hold data on auctions based on 5 world regions (EU, US, KR, CN, TW). Each auction is identified with an ID that is not unique to a region but is unique to a 'realm' in that region. The final requirements are:

  • Able to track historical market prices of individual items up for auction over the past 4-6 months.
  • Able to view the current items up for auction.
  • Able to track sellers of items and their history.
  • Able to create a profile on the site.
  • It is not required to be able to compare regions.

Some stats:

  • Each region can be made up of 240+ realms.
  • Each realm has around 20,000 auctions up for sale at any one time.

My current plan is to work with a database for each region so 5 in total. Each one of those databases would have the following tables:

  • Realms - Holds information about all realms in a region
  • Scan - Holds a snap shot of the current items up for sale in a region. Indexed by realm.
  • Historical - Holds 4-6 months of history for each region. This will include every auction that was seen.
  • Sellers - Holds data on all sellers seen in the last 4-6 months.

I am debating whether it would be better to have just two tables and merging the Scan, Historical and Sellers tables together, then if a user wanted to view just the current scan data or information for a seller it would be calculated from the combined table on the fly.

With me being new at this, I could do with someone with more experience to take a look and let me know if I'm overlooking anything. Keep in mind that I don't know anything about performance and whether the design I have would be acceptable or not.

Anyone interested can see the documentation for the API I am working with here http://blizzard.github.com/api-wow-docs/ I'll have scheduled tasks to import the data and insert it into the database.

Feel free to ask me anything if I wasn't clear enough.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

I'll take a crack at this. Note, that I've just listed fields that are necessary. If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).

While you did state that you would use a different database for each region, I designed this so that they could all work in one database. Right now you may have only 5 regions, but this offers the flexibility to easily add more:

table: region
regionID        varchar(2)
regionName      varchar(50)

Defines data for each realm. I went with a surrogate key (realmID) because it's possible that realmName might not be unique. This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):

table: realm
realmID         int
regionID        varchar(2)
realmName       varchar(50)

Defines data for each user (seller/buyer). I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique. Note, you may also want an address table to store multiple:

table: user
userID          int
userFirstName   varchar(20)
userLastName    varchar(20)
userEmail       varchar(100)

Data for each auction. AuctionID would be a auto-generated ID and the Primary Key. You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):

table: auction
auctionID       int
realmID         int
sellerID        int
auctionPrice    decimal
auctionStart    datetime

Data for each auction that has been completed with a foreign key on buyerID (userID on user table):

table: auctionHistory
auctionID       int
buyerID         int
finalPrice      decimal
auctionFinished datetime

I don't think that you'd need a seperate table for "Scan". That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table. Hopefully, this will help you get started. If I missed/misread anything, please let me know.

share|improve this answer
    
Hi, thanks for taking the time to look through that, your answer was really helpful. I decided to go with one big database for all regions as you suggested, it makes sense and will be easy to expand if I need to compare regions in the future. I don't have a way to find out if an auction has ended at all, the only data that's supplied to me is a current list of active auctions updated every hour or so. I think I will end up having a big table called 'auction' for all seen auctions containing all their data, then a separate table called 'scan' with a foreign key linking to the 'auction' table. –  ChrisO Jan 3 '12 at 4:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.