Database structuring

Hi

I have website which displays tweets (from twitter). Each tweet can
either be given thumbsup or thumbsdown, but only once. What would be
the best way to organzine a database table for this? Its a many-to-many
relationship between tweets and users - should I make a database, lets
call it ‘pairs’, so that each user will have_many :tweets through pairs?

I should have validation coming from two directions - when displaying
the tweet, it should check if the logged in user has already given the
tweet thumbsup/thumbsdown, and every time an opinion is given, it should
check if the tweet has already been reviewed by the logged in user.

I could see this database of pairs becoming very large, but fortunately
there’s really no reason to let users rate tweets after a certain date -
so I could make a periodic job that removes old entries from the table
after a week or so.

I’m sure that I could work out a solution on my own, but this is
somewhere I’d much rather be sure that I’m doing the right way, rather
than having to go back an change it later for performance or
maintainability reasons. Thanks in advance for any input!

Cheers,
–Peter

PS - It would be nice to allow users without accounts to rate tweets. I
could store pair data in their session, but this would obviously not be
bulletproof. One big concern is if robots take advantage, rating 1000s
of times by clearing their sessions and such. Will be googling on this
:slight_smile:

Hi, Peter!

I had the same problem (although completely unrelated to tweets).
I did a has_many through pairs thing.
Also, every 3 months I clean up the old stuff.

Maybe someone knows a better solution?

Cheers,
Gleb

Peter E. wrote:

Hi

I have website which displays tweets (from twitter). Each tweet can
either be given thumbsup or thumbsdown, but only once. What would be
the best way to organzine a database table for this? Its a many-to-many
relationship between tweets and users - should I make a database, lets
call it ‘pairs’, so that each user will have_many :tweets through pairs?

You will need a join table – it’s impossible to do many-to-many
relationships without it. You can call it “pairs”, though I might use
“ratings”. Then:

class User
has_many :pairs
has_many :tweets, :through => :pairs
end

class Tweet
has_many :pairs
has_many :users, :through => :pairs
end

class Pair
belongs_to :tweet
belongs_to :user
end

Notes:

  • You may not need the has_many :through associations at all.
  • You might want to give the associations different names than the
    tables.
  • You will definitely want foreign key constraints in the DB. Use the
    foreign_key_migrations plugin.

I should have validation coming from two directions - when displaying
the tweet, it should check if the logged in user has already given the
tweet thumbsup/thumbsdown,

current_user.tweet_ids.include? @tweet.id

and every time an opinion is given, it should
check if the tweet has already been reviewed by the logged in user.

This is best done with DB constraints.

I could see this database of pairs becoming very large, but fortunately
there’s really no reason to let users rate tweets after a certain date -
so I could make a periodic job that removes old entries from the table
after a week or so.

No, because then you’d lose your rating data.

I’m sure that I could work out a solution on my own, but this is
somewhere I’d much rather be sure that I’m doing the right way, rather
than having to go back an change it later for performance or
maintainability reasons.

Understood.

Thanks in advance for any input!

Cheers,
–Peter

PS - It would be nice to allow users without accounts to rate tweets. I
could store pair data in their session, but this would obviously not be
bulletproof. One big concern is if robots take advantage, rating 1000s
of times by clearing their sessions and such. Will be googling on this
:slight_smile:

Then you’ll need a way to identify anonymous users and make User records
for them (or use a polymorphic association).

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Peter E. wrote:

Hi

So if I have a many-to-many database called pair, each entry will have a
user_id and a tweed_id. This seems like it would be uselessly redundant
if I can always search starting with a known user name.

It’s not redundant. Sure, you’ll always be searching starting with a
known username, but then how will you find that user’s Pairs in the
database if they don’t contain any info linking them to that user?

Wouldn’t it
make sense to instead have a database

You mean a table?

with only one entry for each user,
with (somehow) a corresponding array of tweets?

No. That will contain the same information, but be much harder to
query.

[…]

  • You will definitely want foreign key constraints in the DB. Use the
    foreign_key_migrations plugin.

Why is this? It looks like a useful plugin indeed, but doesn’t rails
already do some of this?

No. Rails does not put foreign key constraints in the DB unless you use
this plugin or something similar.

In a totally unrelated area, I’m using strings
as foreign keys. Is it possible (or a good idea) to do associations
with them?

It’s possible, but Rails is usually happier with integer keys.

[…]

So you would make the user records polymorphic - able to be both for
existing users and anonymous ones?

That’s not what a polymorphic association is. Look it up. :slight_smile:

Couldn’t this just be done by having
a database

You mean a table?

of users, with for anonymous users no name field (for
example) and some kind of identifier instead.

Sure. I suggested the polymorphism because you may actually want to
store different info for anonymous users.

Thanks again,
–Peter

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Hi

So if I have a many-to-many database called pair, each entry will have a
user_id and a tweed_id. This seems like it would be uselessly redundant
if I can always search starting with a known user name. Wouldn’t it
make sense to instead have a database with only one entry for each user,
with (somehow) a corresponding array of tweets?

Marnen Laibow-Koser wrote:

Peter E. wrote:

  • You will definitely want foreign key constraints in the DB. Use the
    foreign_key_migrations plugin.

Why is this? It looks like a useful plugin indeed, but doesn’t rails
already do some of this? In a totally unrelated area, I’m using strings
as foreign keys. Is it possible (or a good idea) to do associations
with them?

> > PS - It would be nice to allow users without accounts to rate tweets. I > could store pair data in their session, but this would obviously not be > bulletproof. One big concern is if robots take advantage, rating 1000s > of times by clearing their sessions and such. Will be googling on this > :-)

Then you’ll need a way to identify anonymous users and make User records
for them (or use a polymorphic association).

So you would make the user records polymorphic - able to be both for
existing users and anonymous ones? Couldn’t this just be done by having
a database of users, with for anonymous users no name field (for
example) and some kind of identifier instead.

Thanks again,
–Peter