relation & query

[djhugovic]

Hello,

I'm relative new to sql and databases and the last few weeks I learned
myself a lot. I'm trying to make a hotel reservation application.

I have a database with a table Booking, a table Room, a table
RoomsPerBooking. So a booking contains date/time etc and a field
RoomsPerBookingID. The table RoomsPerBooking contains number of
persons, unitprice etc. and a field ID and a field RoomID. The table
Room contains data like name, notes etc.

now i have two questions:

First about relations:

The table Booking has relationship: PK table RoomsPerBooking - ID <-->
FK table Booking - RoomsPerBookingID.

The table RoomsPerBookingID has relationship: PK table Room - ID <-->
FK table RoomsPerBooking - RoomID

Is this relationset good for my purpose? I think it is, but I am not
sure.

The second question is:
How do I get available rooms per night

I came this far.... what are the "some statements"?
CREATE PROCEDURE dbo.GetAvailableRooms
(
@BeginDate DATETIME,
@EndDate DATETIME
)
AS

SELECT Room.*
FROM Room
WHERE Room.ID NOT IN (
SELECT DISTINCT room.ID
FROM Room room JOIN RoomsPerBooking roomsPerBooking
ON room.ID = roomsPerBooking.RoomID
--Some statements--
WHERE booking.FromDate <= @EndDate
AND booking.ToDate >= @BeginDate)
GO

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Why do you have multiple names for the same data element? Why do you
use a singular name for a set of Rooms? Why did you use id and
room_id when the standard way of referencing a room is a "room number"?
Why do you use aliases that are the same as the base table names?

After you clean up the schema a bit, look at using a Calendar table.