DB Design Question

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

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

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

Adam Walters 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.

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