Web Form to DB Question?

[enderl25]

i am currently working on a web form/database development project, and i could use a little advice. simply, i am putting together a reservation system (via web forms), which input reservations into a database. one of my requirements is that i allow users to select a series of arrival & departure dates (i.e. 10/10/2002 - 10/12/2002 & 10/14/2002 - 10/16/2002) all at once. i am a bit unsure of how i should implement this... both on a web form, as well as a in a field(s) in a database. any suggestions would be sincerely appreciated.

ritz

[enderl25]

dave,

i am hoping to allow a user to indicate the following:

-----------------------
arrive: 10/10/2002
depart:10/12/2002

and

arrive: 10/14/2002
depart:10/16/2002
-----------------------

this is a scenario where the user wants to:

stay for 2 days,
leave for 2 days,
and stay for 2 days after that.

obviously, i would only want to charge them for the 4 days that they stay.

i am having trouble seeing how your example of:
-----------------------
arrival_begin
arrival_thru

departure_begin
departure_thu
-----------------------
fits this scenario

[enderl25]

dave.

i am hoping that i will be able to store these date-ranges (if you will) all inside one field of my db. something like an array perhaps. see the big conflict here for me, is that i need to allow for an unspecified amount of these arrival-departure periods.

consider the following issue:

---------------------------------------------------------------------
i place 5 arrival, 5 departure fields in my db (allowing for up to 5 different arrival/departure periods).

someone who only wants to stay for 2 days, 1 time, no more, signs up.

i will end up with 4 empty fields in that newly created record.
---------------------------------------------------------------------

i would really like to stay away from this as null values are typically a sign of a poor db design.

do you have any suggestions on a good way for me to take care of this problem, in light of these concerns?

ritz

[Rodders]

I would suggest having two fields for the dates in your database - ARRIVAL_DT and DEPARTURE_DT. Then I would suggest having a unique key field for each stay, possibly STAY_ID. The table would also need a primary key for the customer/person who is staying:

CUSTOMER_ID
STAY_ID
ARRIVAL_DT
DEPARTURE_DT

OK, so now you can list each stay separately in the database. You might want to add some validation so that the dates don't overlap, i.e. ARRIVAL_DT cannot be before the previous DEPARTURE_DT. (note: don't order by STAY_ID incase the book the stays in a random order).

Here's some possible values:

001|1|11/22/2002|11/24/2002
001|2|12/01/2002|12/05/2002
001|3|11/28/2002|11/30/2002

It will then be easy to go through each row and calculate the number of days that the person stayed for. To detect overlaps you will need to just check that a new ARRIVAL_DT is not between any of the other ARRIVAL_DT/DEPARTURE_DT ranges.

I hope that helps.