# DB Design Question

**URL:** <https://www.ruby-forum.com/t/db-design-question/125362>\
**Category:** Rails\
**Created:** [January 8, 2008, 4:53pm UTC](https://www.ruby-forum.com/t/db-design-question/125362 "2008-01-08T16:53:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 4:53pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/1 "2008-01-08T16:53:38Z")

</div>

Not sure if this is the best place to post this, but here goes:

I am developing a test application which will simulate a movie  
cataloging system in which you can lend movies to other users you are  
friends with. To do so i was going to have RentedMovies model with the  
following fields:

owner\_id (user\_id)  
library\_movie\_id (movie which is being lent out)  
borrower\_id (user\_id) --\> user\_id of who is borrowing the movie  
state --\> [overdue, out, returned]  
due\_date (datetime) --\> due date for the movie  
returned\_at  
created\_at

I got hung up thinking about what to do when a movie is “returned”. I  
would like to keep a history of rentals, so i was wondering if i should  
keep all the records in this table and just give them the state  
“returned”, or if it would be better to create a 2nd tabled,  
returned\_rentals or something, to keep the historical records of  
rentals?

Anybody have any suggestions? I’m wondering what advantages there might  
be in either approach. All suggestions are welcome and appreciated.  
Thanks!

-Adam

---

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 5:12pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/2 "2008-01-08T17:12:17Z")

</div>

create\_table :movies do |t|  
t.column “user\_id”, :integer  
t.column “rental\_id” :integer  
t.column “title”, :string  
t.column “state”, :text  
t.timestamps  
end

create\_table :rentals do |t|  
t.column “movie\_id”, :integer  
t.column “returned\_at”, :datetime

#use created\_at as “rented\_as”  
t.column “created\_at”, :datetime  
end

#movies:  
has\_many :rentals

def last\_rental  
Rental.find(:first,  
:conditions = [“movie\_id = ?”, self.id],  
:order =\> “created\_at DESC”)  
end

#rentals:  
belongs\_to :movie

#Then you can do:  
movie = Movie.find(:first)  
movie.rentals  
movie.last\_rental.returned\_at  
movie.rentals.size

---

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 5:36pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/3 "2008-01-08T17:36:05Z")

</div>

why did you add rental\_id to movies? and my rentals also needs an  
user\_id to denote what user is borrowing the movie

---

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 5:42pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/4 "2008-01-08T17:42:25Z")

</div>

Adam W. wrote:

> why did you add rental\_id to movies? and my rentals also needs an  
> user\_id to denote what user is borrowing the movie

Of course there should be no rental\_id in movies. And of course you’re  
right that rentals also should have a user\_id. Actually the user\_id  
should be in just in the rentals, not at the movies at all…

Really sorry about that. I think I’m just a bit too tiered to be allowed  
to answer peopels questions.

---

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 5:47pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/5 "2008-01-08T17:47:03Z")

</div>

No problem 🙂 I guess i should make sure my question is clear, adn that  
is this:

Should i have two tables or one?

one table:  
when a movie is returned, i simply change the state of the rental to  
“returned”

two table:  
when a movie is returned, i could have a 2nd table that is  
rental\_history. I could create a new record in rental\_history, and then  
delete the record from rented\_movies.

I’m just wondering if one approach is better than the other if anybody  
has an opinion

thanks

---

<div class="post-metadata">

**Author:** ![hirodusk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/6f9a4e/32.png) [@hirodusk](https://www.ruby-forum.com/u/hirodusk)\
**Post date:** [January 8, 2008, 6:36pm UTC](https://www.ruby-forum.com/t/db-design-question/125362/6 "2008-01-08T18:36:40Z")

</div>

On Jan 8, 8:47 am, Adam W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> delete the record from rented\_movies.  
> I gave a similar reply on another thread: Ideally, just because an  
> object changes state, it shouldn’t change its class. Similarly with a  
> database row: it shouldn’t change tables just because its state  
> changed.

Pragmatically, having one table makes it easier and faster to report  
on all rentals and it will make your code simpler and less buggable.  
The only reason I might use two tables is for speed, but that’s not an  
issue for you yet.

///ark