CODEWITHSUNDEEP
mysqlruby-on-railsdatabasepostgresqlactiverecord
tom
tom

Reputation: 718

Rails database structure and optimisation advice

My Rails app is a system of Users transacting items between each other. Users bid on items and then the owner approves a bid which creates a transaction. Users can then write comments (messages) on transactions to organise things outside the app. The way I have it structured so far is like so:

User
    has_many :items
    has_many :bids

Item 
    belongs_to :user
    has_many :bids

Bid
    belongs_to :item
    belongs_to :user
    has_one :transaction

Transaction
    belongs_to :bid
    has_many :messages

Message
    belongs_to :transaction
    belongs_to :from, :class_name => "User"
    belongs_to :to, :class_name => "User"

I think this works well in terms of reducing redundancy, but I'm having a few issues here getting data out efficiently. For instance, to retrieve a user's "given items" (i.e. users' items for which there exists a transaction on one of the bids) I am doing:

user.items.joins(:bids).where("bids.id" => Transaction.select("bid_id"))

or received items:

Item.joins(:bids).where("bids.user_id" => user.id).where("bids.id" => Transaction.select("bid_id"))

which seems to be pretty costly for the information I want.

More importantly I want to aggregate users' transactions and show a ratio of given to received items, which might show up next to a user's name on any given page. At the moment what I'm doing is fetching and counting all the users' given and received items above and then dividing (which is extremely costly...). I was thinking of just having a column on the User table with received_count and given_count which updates every time a Transaction is created or destroyed, but this seems unreliable.

My question is, is there a better way to structure my data so that getting information like a user's transactions is much simpler, while keeping it normalized?

Thanks!

Upvotes: 1

Views: 125

Answers (2)

Thomas R. Koll
Thomas R. Koll

Reputation: 3139

Drop the strict normalization. For an archival model like Transaction you should at least store the two^user_ids (sender_id, receiver_id) and the item_id on it. A Bid won't change its item or user so you're pretty save here. You might even want to store the users' names on it if you display those names often.

Denormalized databases are not bad, and once you get to use a database like mongodb or couchdb you will use it more often.

Upvotes: 1

Joel Friedlaender
Joel Friedlaender

Reputation: 2191

Often normalising and performance are trade offs. In your case I think it makes sense to add the extra columns to the Users table to effectively cache the results. You could also add an extra column to items to show if they have had a bid with a transaction.

Normalising is really a base principle, and not something you should feel like you need to stick to for correctness, caching commonly used results even if it creates redundancy is also a really good approach.

The only other option would be to actually use caching (eg. memcached). This would let you store the counts in an actual cache, and just "bust" them when a new transaction occurs for that user.

Upvotes: 2

Related Questions