# Database structuring

**URL:** <https://www.ruby-forum.com/t/database-structuring/173965>\
**Category:** Rails\
**Created:** [August 18, 2009, 7:46pm UTC](https://www.ruby-forum.com/t/database-structuring/173965 "2009-08-18T19:46:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![peterbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/bc79bd/32.png) [@peterbe](https://www.ruby-forum.com/u/peterbe)\
**Post date:** [August 18, 2009, 7:46pm UTC](https://www.ruby-forum.com/t/database-structuring/173965/1 "2009-08-18T19:46:34Z")

</div>

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  
🙂

---

<div class="post-metadata">

**Author:** ![peterbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/bc79bd/32.png) [@peterbe](https://www.ruby-forum.com/u/peterbe)\
**Post date:** [August 18, 2009, 10:31pm UTC](https://www.ruby-forum.com/t/database-structuring/173965/2 "2009-08-18T22:31:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![peterbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/bc79bd/32.png) [@peterbe](https://www.ruby-forum.com/u/peterbe)\
**Post date:** [August 18, 2009, 10:51pm UTC](https://www.ruby-forum.com/t/database-structuring/173965/3 "2009-08-18T22:51:23Z")

</div>

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  
> 🙂

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![peterbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/bc79bd/32.png) [@peterbe](https://www.ruby-forum.com/u/peterbe)\
**Post date:** [August 18, 2009, 11:27pm UTC](https://www.ruby-forum.com/t/database-structuring/173965/4 "2009-08-18T23:27:00Z")

</div>

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

> 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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![peterbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/bc79bd/32.png) [@peterbe](https://www.ruby-forum.com/u/peterbe)\
**Post date:** [August 18, 2009, 11:14pm UTC](https://www.ruby-forum.com/t/database-structuring/173965/5 "2009-08-18T23:14:15Z")

</div>

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