Database Design – Loaner System – Customer loaning multiple items

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!

Answer

Your second choice is the correct choice for a relational database system.

You are determining the key entities that the database needs to store, and then designing them with the correct attributes.

The first method is storing multiple things in a single field, usually an indication of poor design.