Sign in to follow this  
BUnzaga

[web] One table for ALL games, or one table PER game?

Recommended Posts

BUnzaga    267
I'm about to make an online high score system for a simple little game we made, but I want to plan for the future. Should I make one database with tons of tables to handle every single game I will ever make and host on my site, or should I make a separate database per game? Should I keep the score tables separate from the other information in the game such as inventory, etc or is it ok to just cram them altogether? :) Please let me know which way to go!

Share this post


Link to post
Share on other sites
Katie    2244
It would probably make sense to put them all in one place to start with but allow flexibility later on; I would suggest a scheme which encodes the game name in the score submission/high scores request and lets the server decide where the data goes. Presumably to start with the data is relatively simple (name/score/date) but later on it might become a lot more complicated (name/score/date/time/achievements_unlocked for example) and you might want to implement more complex structures.

If you use a "soft" protocol for the transmissions (eg, JSON), you'll be able to add fields to the communications system relatively easily without breaking your existing games.

Share this post


Link to post
Share on other sites
BUnzaga    267
Awesome idea man, I mean woman. it doesn't sound respectful for some reason LOL!

Lets just leave it at Awesome idea!

I don't know why I over complicate things like this. Using a php script to decide what goes where is a great idea.

Share this post


Link to post
Share on other sites
Ravyne    14300
Ultimately, you want your database to be "normalized" which means that, essentially, to avoid duplicating data, data that *really* goes together is stored together, and when we need to pull together related data, we cross-reference between it. In fact, this is the entire reason that Relational database systems exists, and the foundational model under which they operate.

In your example you want to talk about high scores -- this gives us a good place to start.

Each "score" entry consists of not just a numerical score, but also implies the game to which it belongs, the user which achieved it, and possibly other data, such as the date and time it occurred, the level they were one, or other notable information.

The date/time and other notable info belongs to the high score itself -- that is, its part of the same "event" or data-point, and it doesn't have any other value on its own, or to any reference that will outlive the score itself. For this reason, this information can probably be made a part of the same record with no ill consequences to database maintainability. We may want to give ourselves a way to add additional custom data -- so we either make one score table for each game, or a unified score table in which each score contains its own unique key that references an additional per-game table.

Now, we also cross reference the associated game via a key that is unique to that game. The game has a whole bunch of information associated with it -- title, description, rating, release date -- stuff that drives content on your website or maybe feeds third-party sites that sell your game. Now, since we have a key to this game, we can get at it from our high score, and from there we can get any piece of data we associate with it. On the game's webpage, it becomes easy to display the 10 highest scores with a simple SQL query as long as we know the games key value.

Similarly for each player's profile page -- we can pull up their personal high-scores and then compare them with the all-time high scores of all other players, just their friends, or to any other single player. A player record might contain their login name, public profile data and unique key value -- and act as a jumping off point for their buddy list, registration information, private registration data (address, credit card info) and all the other games they own.

The key to designing a database that will last many years, iterations and products, is careful planning through the careful partitioning of data, and by looking forward to the types of things you might want to do in the future. This doesn't mean laying groundwork for the future now, but rather to avoid designs which make assumptions about how data will be used in the future -- for example, don't assume that the high score list will only ever show 10 scores -- what if you want to change the high score list to 20, or you need the top hundred scores within a given timeframe to decide who gets the prizes in a promotional contest? Its better to keep all scores, or at least the top N scores for each player (or maybe the more-complex, 'keep at least one high score for each player, but more scores if they've made the top X', which could be done with a stored procedure or other programmatic means.)

This advice is orthogonal to Katie's -- as her advice was more about transmission than persistence -- and in fact her advice prescribes a layer of abstraction that will allow your database to evolve behind the scenes if and when necessary, and goes hand-in-hand with good database design as it further removes presentation from implementation. These types of neutral, text-based data formats like JSON (or YAML and others) even make decent storage formats for things like local high score tables -- which means your code can have one less thing to worry about [grin]

Share this post


Link to post
Share on other sites
leiavoia    960
The key point here is Database Normalization. Ravyne already pointed out most of the good stuph, but i still think you could use a good primer on the topic. I'm just providing a handy link. Make sure you understand database normalization before you start creating a database. I'm only saying this because i undertook a project that sounds very similar to yours and i wish i had known back then!

There's lots of articles on the topic. Google with help you with others.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this