For the two relations, Customer and Equipment:
There is a limited number of equipments (stock) and upon hiring I need to store the date of hire and the expected return date.
I sketched this:
Table Customer
customer_id
Table Equipment
equip_id
available_stock
Table Hire_Equipment
customer_id
equip_id
hiring_date
expected_return_date
What I'm not sure about is what is the primary key of Hire_Equipment?
If it is customer_id and equip_id then the customer wont be able to hire the equipment again in the future?
Did I mis-designed the schema? do you suggest a better approach?
Best Answer
In a practical system (as opposed to a textbook problem) you would want to have some kind of transaction identifier on any transaction like hiring one or more pieces of equipment. Assuming you are running an equipment hiring business, your transactions would probably have to account for letting out multiple pieces of equipment at once. Therefore you need to add an order table as follows:
and your Hire_Equipment table would change as follows:
Note that the
hiring_date
has been normalised up to theOrder
level, butexpected_return_date
has been left at the item level, since you might want to allow a single order to contain a mixture of rental periods.This design allows a customer to hire a piece of equipment more than once on a given date but it prevents hiring the same equipment more than once on the same order - which is what you would expect.