mysql - Database design: storing full transaction history AND quick lookup of current status? -

this pretty basic question, i'm beginner :)

i'm building library database using django & mysql. database has concepts of books , users, , of transactions, user takes out or returns book.

i need able to:

• get full list of transactions particular book - checked out , when
• get current status of book, quickly.

i had thought of using tables this, there problems design?

class book(models.model):     name = models.charfield() class user(models.model):     name = models.charfield() transaction_types = (     ('i', 'check in'),     ('o', 'check out'), ) class transaction(models.model):     book = models.foreignkey(book)     user = models.foreignkey(user)     type = models.charfield(max_length=1, choices=transaction_choices)     date_added = models.datetimefield(auto_now_add=true) 

this work ok getting full list of transactions. if want current status of book, i'd have search through book's transactions , find recent.

is sensible? future-proof?

i don't foresee database getting big, there more efficient way store current status of book, without de-normalising database?

all suggestions gratefully appreciated!

a well-designed database won't search through book's transactions find recent. @ least, database designer create indexes, , dbms (probably) use them.

mysql can that; i'm not familiar enough django know whether it's smart enough mysql that. i'd little surprised if weren't, though.

as whether it's sensible , future proof--from local library i've checked out out books, cds (of both music , books), cassette tapes, maps, dvds, video games, educational software, toys, , framed artwork.