This sounds like a fun project! We limit threads to only one question, so I'll tackle the table design as that is the foundation for the database and is very important to make a functional database. If you could ask the question about the 2 weeks and the emails (which are totally possible) in two additional threads, we would be more than happy to assist you. (Breaking up the threads helps keep the threads clean and easier for others to benefit from them.)
A word of advice about table/field/control names: Don't use spaces or special characters (other than underscore "_") in the names. You can use camel case (first letter of each word capitalized for readability, i.e. MyField) or use the underscore in place of the spaces. It will make it easier to design your database.
Just to be clear, is the Swipe ID the ID of the person checking the books out/in? Also, I assume that each person can check out more than one book at a time? Something to consider, if you don't create profiles for each person checking out a book, you will have to enter their name and email address every time they check out a book. If you did create a profile for each person, that information could be stored and then you would be able to pull up that information the next time they come in and don't have to risk typing in a name or an email address incorrectly each time. To me, this seems worth the slight amount of extra time to create the profile once and then not have to again. This is totally up to you, but it does effect the table design and not having the profile makes generating the email address harder. It also makes it harder to find all the books that a particular person has out as you would have to type the person's name the exact same way each time (period after middle initial or not, including the middle initial or not, using a nickname, OConner vs O'Conner, etc.).
Enough about the profile. What I would do is to create a table that would hold the information about the checking out process and a table of the books that were checked out. For example, I'll name the first table tblCheckOut and the second table tblBooksCheckedOut. Fields would be as shown below
- tblCheckOut
-
CheckOutID
-
SwipeID
-
CheckOutDate_Time
-
Patron
-
EmailAddress
If you decide to use patron profiles, then make Patron a number field and leave out EmailAddress. Otherwise, make Patron a text field and leave EmailAddress.
- tblBooksCheckedOut
-
BookCheckID
-
CheckOutID_fk
-
BookID
-
DueDate 'If books cannot be renewed, then leave out this field as it is just a calculated field.
-
CheckInDate
Relate the tables using the tblCheckOut.CheckOutID = tblBooksCheckedOut.CheckOutID_fk one-to-many relationship. This will allow you to checkout books with a single swipe by utilizing subforms.
The check-in process would use a query to find all the books still out (CheckInDate Is Null) and then try to find a match for the BookID you typed in. If no match, the book has already been checked in or it never was checked out (this does happen occasionally at my library).
Also, since you are the one who really knows what you are dealing with and what you are wanting, it makes sense to give you the tools to help you decide for yourself what is needed. Here is a link that will help you know how to design your tables correctly:
Database Normalization and Table Structures. You are still totally welcome to ask for help here, but this will help you understand the reasoning behind the suggestions.
1
