Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Petehe
Starting Member
20 Posts |
 Posted - 2004-06-09 : 01:00:42
|
| Hi all,I am working on a vehicle booking system.But the requirement is we need to make it work with recurring bookings (such as the car will be booked out every Monday morning).Can anyone give me some idea how to design database to cope with this?Your help is much appreciated. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-09 : 01:22:50
|
| Have a table of vehicles:VehicleIDVehicleTypeVehicleRegoHave a table of Recurring bookings:RecBookIDVehicleIDFrequency (ie weekly daily)StartTime EndTimeStartDateEndDateMake a table of regular bookingsBookingIDVehicleIDStartBookingEndBookingThen to bring them together, have create a stored procedure to give you a list of all bookings over a given period.Should do the trick.Tim |
 |
|
|
Petehe
Starting Member
20 Posts |
Posted - 2004-06-09 : 01:40:33
|
| Thanks for your help, can you give me a bit more details for stored procedure. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-06-09 : 02:26:56
|
| This comes up occasionally.The only problem with Tim's example is if you wish to change one instance of an appointment. I.E. If the car is NOT booked out on a particular monday.Here is a good thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10761&SearchTerms=recurringAnother option is to look at the sysjobschedules table in the MSDB database to see how SQL Server jobs are handled.Damian |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-09 : 02:42:21
|
| Thanks Damian - I also didn't realise how much of a bastard the SP was going to be to write and debug......Tim |
|
|
|
|
|
|
Recurring booking