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

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 Ehrlich wrote:
<snip>

* 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?

<snip>

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).

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