Archived

This topic is now archived and is closed to further replies.

Hoser

Database gurus - design question

Recommended Posts

I''m in the process of designing a fantasy type sports game for the web using PHP with the data storage using MySQL. My question is regarding how I should structure my tables. I have two solutions, but I''m not that familiar with DB coding or design, so I was just looking for any insight. This is the situation. Basically, each user will have a roster of players, and the ability to trade players amongst themselves (or buy players - free agents etc...) My question regards how to keep track of all the transactions. I want to be able to list the details of all transactions by a particular player if needed. Here are the two solutions I have come up with. 1) Have a single "transaction" table. Everytime any user makes a trade or anything affecting their roster, an entry will be made in the transaction table. 2) Each player will have their OWN transaction table. Now which solution is more efficient? Also, what happens if the user base grows to a large number? If I have a large number of users, will it take too long to search the table for case 1? Or, will the DB become to messy with so many tables? How "large" is too large for separate tables? Would adding a linked-list type scheme to the first solution help? Ex. In the user information table, I can have an entry listing the "first" transaction # and last transaction number made by the user. In the transaction table, I can have an element which holds an index to the "next" transaction. Any insight is appreciated. I am really looking for a solution that won''t "paint me into a corner" if the user base growns large. Thanks, Hoser

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Two things to consider:

Keeping the table small isn''t _necessarily_ a good thing.

If you wanted to see the last ten transactions overall, you''d have to grab a list of the most recent transaction from EVERY PLAYER''S TABLE.

I''d say keep it as one, but it really depends on what oyu want to do with it.

Share this post


Link to post
Share on other sites
Well, the issue is more from the users'' perspective. They may want to see a list of THEIR last ten transactions, or their last 100 transactions etc...

The thing is, what happens if the user base grows large, say (completely hypothetical) 250,000 users. That one table will have TONS of entries. Would searching the table be really slow without using the linked-list type scheme? At that number of users, having that many individual tables would be cumbersome to say the least.

So, any suggestions on handling an enormous amount of users, and still having access to all the individual user information easily?

I guess the solution I''m looking for is somewhat similar to how a bank tracks all of the user data. Anyone have any insights there on their DB structure?

Share this post


Link to post
Share on other sites
Using a postgres database to query ~30 million records and perform operations based on *every* record takes approximately 15 minutes on our backend. MySQL has pretty good performance, so I suspect that searching 250,000 records shouldn''t be that big of a problem.

You can test it yourself by populating a table with a large enough set of apropriate randomly generated data. From there execute your queries and use EXPLAIN statements to inspect how your queries are executed.

Share this post


Link to post
Share on other sites
That decision would depend on how you think most people will be viewing information. The way you''ve said it, it sounds like you only want players to be able to view their own transactions. In which case the data should be in the player table. I don''t really think it will make that much difference, MySQL is pretty good, and it should be able to handle look thru multiple tables. You might want to consider saving transactions in a seperate table, and creating an array of "transaction keys" in the player''s table.

Share this post


Link to post
Share on other sites