I am implementing a loaner system and would like find out the most efficient / database standard structure technique to use for the loan records. Let me start with the basics:
I have two tables:
Customer_Record(id, f_name, l_name, p_number)
Loaner_Pool(id, model, serial_n, status)
The idea is that the loaner pool consists of a list of gear like: Mouse/Keyboard/Laptop/Batteries/Camera/etc. Status represents if the item is booked out or not. What I would like to know is how to design the loan record table(s) that will hold the individual loans of the customers. I had two ideas of doing this:
Idea 1
Have one record entry that has a column called "gear_list" which will hold the id's of all the gear booked out by the customer separated by a symbol like "," such as:
Table: loans(id, customer_id, gear_list, date, status)
Example Loan:
loans[102, 30001, "23, 34, 949, 293", timestamp, "out"]
With this idea I have keep my entry's low. However I then have to process the "gear_list" column every time in PHP in order to separate the id values for the gear loaned out then do individual SELECT's to obtain the data.
Idea 2
Have each item the customer wants to loan as a separate entry in the table. The table would look the same as Idea 1 but "gear_list" would only hold ONE id for each loaned item.
Table: loans(id, customer_id, loan_pool_id, date, status)
Example Loan: loans[102, 30001, "23", timestamp, "out"]
loans[102, 30001, "34", timestamp, "out"]
loans[102, 30001, "949", timestamp, "out"]
loans[102, 30001, "293", timestamp, "out"]
For this idea the amount of records will increase by alot after a number of loans have been processed. The column "gear_list" becomes "loan_pool_id" since it only holds one id. The benefit is the ability to have only one id in the "loan_pool" for easy manipulation/reporting.
Is there a better way of handling the situation?
Thanks in advance!
