Include Account info in Users Database

Question

Hi,

I'm building my first Rail App and are beginning to setup my database. I have a simpel Users table containg name, e-mail (login), password and so on.

The site lets the users bet each other (with monopoly money), so I need to add information about the users current winnings and other delicate matters to the database.

Question: Should I place these information in the Users table or should I create an Account Table and insert the information there. I'm lacking the knowledge to see if it should raise any security issues to insert the information in the Users table.

Kind regards

Kenneth
Denmark

Answer 1

Their actions are a different entity then the actual user themself. Keep user information such as profile info in one table. All other entities should be seperated. So you want to seperate that into another table and create a foreign key back to the users table to determine which row in this accounts table belongs to the user.

Answer 2

From a transactional point of view, it is much better to have subsidiary records that track operations than to modify a single record in place.

For instance, if you are having a system where users can place "bets", then it stands to reason there would be some kind of Bet class that defines a bet between two people. As users create bets, the associated list will grow. In Rails parlance, it looks like this:

class User < ActiveRecord::Base
  has_many :bets
  has_many :bet_pools,
    :through => :bets
end

class Bet < ActiveRecord::Base
  belongs_to :user
  belongs_to :bet_pool
end

class BetPool < ActiveRecord::Base
  has_many :bets
  belongs_to :winning_bet
  belongs_to :winning_user,
    :class_name => 'User',
    :through :winning_bet,
    :source => :user
end

The amount of each bet is stored in the Bet record, and the BetPool represents the aggregate bets made towards a particular wager, though you may have a different term for such a thing.

A winner can be designated by assigning the bet_pool.winning_user association if and when the bets are settled.

If you want to know a user's "winning record", then it is as easy as tabulating all the betting pools that they've won and adding up the amounts.

The reason you don't want to be constantly adjusting some property on the User record is because two independent processes may want to tweak that value and you can end up with a race condition if the SQL is not implemented properly. For instance, a user might place a bet and win a bet in a very short period of time.

If a user started with $1000 and two operations occur simultaneously, then this could happen:

# Process A intending to add $500
user.balance = user.balance + 500
user.save

# Process B intending to deduct $100
user.balance = user.balance - 100
user.save

Done sequentially you'd expect the balance to go from 1000 to 1500 and then down to 1400, but the second process started with the original value of 1000 when it loaded, then adjusted to 900 and saved, over-writing the result of the first.

There are methods like increment and decrement for ActiveRecord that can help with this sort of thing, but the best results are achieved by simply tabulating as required.