Evening all,
I'm looking for the best / most efficient way to record booking/availability dates in a SQL 2008 database. Booking/Availability can be a single day, consecutive days (not including weekends) or non consecutive days. At the moment I'm recording every date the booking contains in seperate rows. I have to wonder if this is the most efficient approach or is there a better way to achieve this...?
Many thanks
Dave
Hopefully this will give you an idea of how its layed out....
CREATE TABLE Availability
(
A_ID Int NOT NULL Primary Key,
A_BookingID Int,
A_SupplierID Int,
A_BookingDate Date,
A_BookingCode VarChar(15)
)
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1001', '2001', '3001', '2015-11-18', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1002', '2002', '3002', '2015-11-18', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1003', '2002', '3002', '2015-11-19', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1004', '2002', '3002', '2015-11-20', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1005', '2003', '3003', '2015-11-18', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1006', '2003', '3003', '2015-11-20', 'Booked')
INSERT INTO Availability (A_ID, A_BookingID, A_SupplierID, A_BookingDate, A_BookingCode)
VALUES ('1007', '2003', '3003', '2015-11-23', 'Booked')