4

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?

3
  • 1
    You can make the (customer_id, equip_id, hiring_date) the PK. Then a customer can't hire the same equipment twice in the same day. Commented Oct 28, 2012 at 10:56
  • Or you can add a surrogate primary key. Commented Oct 28, 2012 at 10:56
  • @ypercube And I guess adding the number of hired equipments to Hire_Equipment table?
    – Chiron
    Commented Oct 28, 2012 at 11:17

1 Answer 1

3

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:

Table Order
order_id
order_date

and your Hire_Equipment table would change as follows:

Table Hire_Equipment
customer_id (PK)
order_id (PK)
equip_id (PK)
expected_return_date

Note that the hiring_date has been normalised up to the Order level, but expected_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.

4
  • Why Order table doesn't have 'customer_id' FK ?
    – Chiron
    Commented Nov 3, 2012 at 12:08
  • @Chiron - It depends on your business rules for orders. If everything on one order must be for the same customer then you should move customer_id to the Order table instead of Hire_Equipment. Your original model didn't require this restriction even though it is a reasonably common rule, so I didn't presuppose it.
    – Joel Brown
    Commented Nov 3, 2012 at 19:43
  • If I moved customer_id to Order table, what is the primary key of Hire_Equipment ? order_id and equipment_id ?
    – Chiron
    Commented Nov 3, 2012 at 20:13
  • @Chiron - Yes, that's right. Hire_Equipment would be identified by the FKs to Order and to Equipment.
    – Joel Brown
    Commented Nov 3, 2012 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.