I'm pretty new to databases but am trying to implement one for persistent storage in a small python application I'm writing. The application records the loan of access cards to students. I've identified Loan, Card and Student as objects and they map to tables:
Now I just need some pointing in the right direction tightening up the design so I can convert it to python/sqlalchemy code. When a student borrows a card a new loan is created (and a new student and card if they don't already exist). A card can belong to many loans (after a card is returned it can be reused) but a loan can only have one card so card to loan is a one to many relationship. Student to loan is also a one to many relationship as students can have many loans but a loan is only given to one student. Card and student don't have a relationship to each other. When I query the database I am going to have to join the tables on the FKs to get all the data I need to display in the application. Can I do multiple joins?
This is my understanding of what I need and I am ready to implement it in python code. I am really looking for someone to review the above and check I am correct and have not misunderstood anything. Is the above in 3rd Normal form? (I think it is). Do I have any problems here? Am I fine to join multiple tables?