Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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   

Table Equipment   

Table Hire_Equipment

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?

share|improve this question
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. – ypercubeᵀᴹ Oct 28 '12 at 10:56
Or you can add a surrogate primary key. – ypercubeᵀᴹ Oct 28 '12 at 10:56
@ypercube And I guess adding the number of hired equipments to Hire_Equipment table? – Chiron Oct 28 '12 at 11:17
up vote 3 down vote accepted

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

and your Hire_Equipment table would change as follows:

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

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.

share|improve this answer
Why Order table doesn't have 'customer_id' FK ? – Chiron Nov 3 '12 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 Nov 3 '12 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 Nov 3 '12 at 20:13
@Chiron - Yes, that's right. Hire_Equipment would be identified by the FKs to Order and to Equipment. – Joel Brown Nov 3 '12 at 21:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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