Property & Vacancy Table Design

[CutAndPaste]

Hi,
I've got the basic structure as below:

tblProperty (1:M with tblAccommodationEvent)
tblUser (1:M with tblAccommodationEvent)
tblAccommodationEvent

tblAccommodationEvent has foreign keys of PropertyID & UserID and StartDate & EndDate fields.

Each property can be used by each User many times by creating an Accommodation Event. A user can't have more than one Accommodation Event at a time and a Property can't have more than one User assigned to it at a time.

What I'm trying to prevent is creating an AccommodationEvent if either the User has an existing AccommodationEvent or if the Property has an existing User.

How should I approach this? Indexes?

Thanks

 

[llkhoutx]

One way would be to use "mutliple key indices" with no diplicates allowed.

However, I can envision events which ovlerlap for a user.