DB Design Issues - Really stumped!!

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

DB Design Issues - Really stumped!!

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2015-02-08 : 13:15:32

Gurus,

We are designing facilities reservation system.

so far, we have:
Facilities table:
facilityId int pk identity seed
facilityName
...
...

Fees
feeId int pk identity seed
feeAmount
facilityId int fk to facilities table
...
...

Reservations
reservationId int pk identity seed
reservationDate datetime
feeId int fk fees table
facilityId int fk facilities table

Reservations table is where user's reservations are saved.

The process follows:

From the app, user selects facility from dropdown
User selects reservation date (usually from date and End date)

Example: user wants to make a reservation for February 18.

So, user selects from date of February 18, 2015 and End date of February 18, 2015.

If there is an available facility for that date, it is displayed and then user can go ahead and make his/her reservation.

If no reservation is available for that date, then users are presented with one month's worth of data to make alternate reservation.

The facilities are available all year round with the exception of holidays.

My biggest problem is designing the date table in such that user's date selection can be compared to existing date.

This has stumped me now for a long period of your time.

Your true expertise is greatly appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-08 : 14:15:35

Any table having a column defined as type date, datetime, smalldatetime or datetime2 can be compared to a date entered by a user. Not sure what the problem is

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2015-02-08 : 15:04:12

hi gbritton

Ok, obviously you have oversimplified the solution but then I could be wrong.

Let's assume that I have this table called AvailableDates.

This table has following attributes:

dateId int pk identitySeed
availableDates dateTime

How do I compare user's date selection to this table given the requirements I mentioned?

In other words, we would like to select facilityName, availableDates, fees, some other fields from facilities fc, inner join fees f on fc.facilityId = f.facilityId where availableDates between paramfromDate and paramEndDate?

How is this going to work?

Sorry if I appear lost.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-08 : 20:35:24

Your query looks like it should work when finished. More or less what I would do.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2015-02-08 : 21:21:54

Sorry to disappoint you sir but I don't believe you.

I don't see how that query works with nothing linking dates table to reservation table to ensure the date user wants to reserve facility on is available.

For me, the search for help continues.

I know there is something missing with my design.

Thanks for your comments.

newballance989
Starting Member

3 Posts

Posted - 2015-02-09 : 02:13:13

unspammed

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2015-02-09 : 20:02:21

silly

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-10 : 16:09:15

" select facilityName, availableDates, fees, some other fields from facilities fc, inner join fees f on fc.facilityId = f.facilityId where availableDates between paramfromDate and paramEndDate"

This is a good place to start. Now, add in a join to the Reservations table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources