Tell me more ×
Game Development Stack Exchange is a question and answer site for professional and independent game developers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am developing an MMOG (not specifically RPG), in this MMOG there are several mini games which the user can play. Users can earn gold from this each of this mini game (depending on their score).

User also can spend their hard-earned gold to buy in-game items (accessories, upgrades, etc).

I am now still confused on how should i design the database which could keep track every transaction in an efficient manner.

What i have in mind at the moment is

  1. I have several tables (Users, Game, Log, Session, etc)
  2. I am thinking of creating another table called savings and spending. Inside it should have column like id, user_id(FK), amount (both for saving and spending respectively) and for saving table there could be game_id (to indicate how much gold a user get from a particular game) while for spending there could be item_id (to indicate how much gold a user spend on a particular item).

This way, i think it would be easy to calculate the current amount of gold a player have SUM(saving) - SUM(spending) from the begining of time.

I had a suggestion to put a column called 'gold' or something similar in Users table and just updates its value everytime a user spend or gather gold. But, i feel that it just too simple to be true.

Can anyone guide me to the right direction?

Cheers

share|improve this question
1  
This is a mainstream programming or DB question and only tangentially game related; you're going to get the best answers from an appropriate source that's most likely not here on gamedev =) – Patrick Hughes Apr 8 at 15:56
For logging transactions in the DB for gold earnings, spendings, etc. what you are looking for is a means of storing "tick data". – Wing Tang Wong Apr 8 at 23:43

closed as not constructive by Byte56, Patrick Hughes, Josh Petrie, eBusiness, Maik Semder Apr 9 at 6:24

As it currently stands, this question is not a good fit for our Q&A; format. We expect answers to be supported by facts, references, or specific expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, see the FAQ for guidance.

1 Answer

up vote 0 down vote

Here's a proven design borrowed from financial systems:

  1. Put Earn*/Spend in one table (transaction). Just have an indicator field to distinguish them

  2. In your User table, add field "balance". This "balance" reflects the user's gold at the start of the day. Determining the true gold balance would then be:

    gold balance = balance +  earn transactions today - spend transactions today

    Having the day's balance at hand allows a more optimized your database query that only includes today's user transactions.

  3. Finally, at the end of each day, update User table balance field.

share|improve this answer

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