Can I have multiple primary keys in JPA

Question

I am using JPA as ORM mapping. I have a booking table that currently has reservationID as its primary key. I have few more columns mainly date, fromTime and toTime.

What I want now is any other booking on the same day and conflicting with the time should not be allowed.

examples:

1. 12-12-2015 10 14 -- allowed
2. 12-12-2015 10 12 -- not allowed
3. 12-12-2015 09 10 -- allowed
4. 12-12-2015 09 11 -- not allowed

What shall I do?

1. use between clause
2. make date + from + to as one more primary / composite key for my table.

I am confused with both the approaches. Which one would be the better approach?

Answer 1

It would be 1) between clause.

This is not a technical but a business logic problem. You want to disallow conflicting booking for one resource. Thus you have to calculate overlapping of two time intervals. These can overlap although they don't have the exact start and to date.

You can use jodatime to calculate overlapping of time intervals:

Compare Intervals (JodaTime) in a list for overlap

Or you can just select overlapping bookings:

select * from bookings where from >= parameter_to and to <= parameter_from

Where parameter_from and parameter_to are the input parameter of the select.

This would be a business validation even before you try to persist a booking.