Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
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. –  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

1 Answer 1

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
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.

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

 
discard

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.