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.
- 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.