mysql - Customize deals app's homepage and emails thanks to user history: how to do it the right way on Rails/postgreSQL? -
i’d customize :
1. order of deals on homepage
2. emails depending on deals user has seen.
thanks people on so, seems best have 3 models , tables "standard_user", "deals" , "deals_participation" in order have many-to-many relationships app need, link table follows:
class dealparticipation < activerecord:base #this means deal_participations table has standard_user_id key belongs_to :standard_user #this means deal_participations table has deal_id key belongs_to :deal #... more logic goes here ... end class standarduser < activerecord::base has_many :deal_participations has_many :deals, :through => :deal_participations # ... more logic goes here ... end class deal < activerecord::base has_many :deal_participations has_many :standard_users, :through => :deal_participations belongs_to :admin_user #... more logic goes here ... end where i’m lost : how should store , table should query data of deals user has participated in:
- should store deals_participation_table ? columns being deals_participation_id/user_id/deals_id, fear deals_participation table highly ineffective query have search enormous number of lines, find user = mathieu45 (example) find corresponding deals , make sort of calculation know kind of deals he’s interested in , use info adjust deals list on homepage (and emails sent him).
- should instead store in users_table have direct access based on user_id deals did ?
- store in table dedicated user_history ?
the schema you've described efficient kind of query you're interested in, provided put correct indices on tables. databases don't behave lists: asking question "which deals did xxx participate in" shouldn't scan whole table, because correctly indexed table know find of xxx's deals.
in order set correctly, here's migrations like:
class createstandardusers < activerecord::migration def change create_table :standard_users |t| t.string :name t.timestamps # more fields go here end add_index :standard_users, :name end end class createdeals < activerecord::migration def change create_table :deals |t| t.references :admin_user # other fields go here end add_index :deals, :admin_user_id # other indices go here... want search on efficiently. end end class createdealparticipations < activerecord::migration def change create_table :deal_participations |t| t.references :standard_user t.references :deal t.timestamps end add_index :deal_participations, :standard_user_id add_index :deal_participations, :deal_id add_index :deal_participations, :created_at end end there's still lots more belongs in these migrations (e.g. should add non-null constraints, uniqueness constraints, etc). point having these indices makes database operations you're describing extremely fast.
