Database Design Advice. - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Database Design Advice.

Author

Topic

jwhelan
Starting Member

7 Posts

Posted - 2003-08-04 : 13:43:02

I have to build a web application for Online Restaurant Reservations.

The application will have registration for restaurants where the restaurant can say how many tables and seats per table they have,
and each restaurant will input how long their average reservation lasts.

I have to come up with a system where the client can search for a specific time (ex: January 2nd, 2003 6:00:00PM (30 Minute increments), and a number of people for the reservation.

I think I have come up with a reasonable solution for the table design, but I have been having a hard time trying to create a sp that will be able to check to see if a table exists for the specified time. One of the reasons I am having a problem is because if a user wants to book for 7 people, and the restaurant has tables of just 4 seats, then I have to join 2 tables together to make 8 seats.

What I have been thinking so far is the following:

Restaurants {
RestaurantID INT Identity PK,
Name varchar,
Address varchar,
OpenTime DateTime,
CloseTime DateTime,
AvgReservationTime int
}

RestaurantTables {
TableID int Identity PK
SeatCount int,
RestaurantID int FK
}

Reservations {
ReservationID int identity pk,
ResTime datetime,
Name varchar,
email varchar,
RestaurantID int FK
}
ReservationTables {
ResTableID int identity pk,
TableID int FK,
ReservationID int FK
}

I greatly appreciate any advice that you can give.

Thanks,
Jason

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 14:41:06

Don't you need tableid in the reservation table?

You'll need someway to track when it's unavailable....and if you're only tracking start time for the res, what's the avg length of time for dinner? I would imagine every restr. is different.

You would need that piece of info....

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-04 : 15:02:36

quote:

Don't you need tableid in the reservation table?

Brett -- read his requirements more carefully, then look at the table just after reservations.

Jason --
Looks pretty good to me. Why don't you try it, put in some sample data, and post those statements here. Then, we can help you write some SQL if you need help.

One thing I suspect you will need is a table of "Times" -- with 1 row per 15 minutes in a day or something like that.

Then, to see all tables for a each restuarant for the hours it is open, you do something like:

select A.resturantID,
B.TableID,
c.Time
from
Restaraunts A
INNER JOIN
Tables B
on
a.restaruantID = b.restaurantID
INNER JOIN
Times C
ON
C.Time between OpenTime and CloseTime

and then, from here you can do a LEFT OUTER JOIN to the reservations to see what is taken and what is available ! Though, as Brett says, you will need some sort of calculation for each reservation of when you expect the tables to become available again.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 15:17:02

OK, But why two tables then?
[qoute]
Reservations {
ReservationID int identity pk,
ResTime datetime,
Name varchar,
email varchar,
RestaurantID int FK
}
ReservationTables {
ResTableID int identity pk,
TableID int FK,
ReservationID int FK
}
[/quote]

The table would just be attribute of the reservation...no?

What else would be kept in ReservationTables?

Also, just a thought, would there be table preferances?

Outdoor, smoking/non smoking, Window seat, Private, Bus Table (the one I usually get)?

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-04 : 15:27:17

Brett --

It's called a MANY-TO-MANY table. A reservation may consist of more than 1 [restaurant] table.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 15:53:23

Long weekend....

[homer]dooooooooooooooh[/homer]

Just one other question...

What happends when The dining time exceeds the allotted period...you going to build in slack time?

Brett

8-)

SELECT POST=NewId()

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-05 : 00:02:42

Jason,

A few suggestions...

1) Do you really need a DataType of INT for the TableID? SMALLINT will be more than enough and TINYINT would probably do it too..

2) RestaurantTable should have a composite PK of TableID and ResterauntID. Choose carefully the order of those two columns (for indexes)

3) In ReservationTables completely REMOVE the redundant and dangerous ResTableID column. Make the other 2 columns the composite PK.

And in the spirit of the never ending debate....

a) Will a Restaurant Name be Unique?
b) Will the RestaurantID, ResTime, Name column set be sufficient for the Key to the reservation table?

DavidM

"SQL-3 is an abomination.."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-08-05 : 03:32:18

Yeah, and will you allow people to book in just for dessert?
Will you allow a wait-list in case bookings fail?

May sound like stupid questions, but even some of the very finest restaurants do this....

(And can I add at this point for any body in Sydney, Darling Mills on Glebe Pt Rd does THE best dessert option - wife and I spent $150 just on dessert! ohhhhhhhh yerrrrrm - if you go for mains aswell you won't be able to fit it in!)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jwhelan
Starting Member

7 Posts

Posted - 2003-08-05 : 07:30:31

Thanks everybody for all your replies.
I have taken into account everything you guys have mentioned and am starting to build the backend of the application.

I will check back later with any progress I may have, but will prolly just have more questions

Thanks
Jason

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 08:57:54

quote:

3) In ReservationTables completely REMOVE the redundant and dangerous ResTableID column. Make the other 2 columns the composite PK.

YES, YES, YES! i can't believe I didn't see that

.... thank you David for noticing this .... probably my biggest pet peeve in database design.

- Jeff

jwhelan
Starting Member

7 Posts

Posted - 2003-08-05 : 10:23:45

Hello again,

I have tried to use your ideas in my backend and have come up with the following problem:

Jeff's SQL works and shows me what tables are available and taken,
But I am unable to see what tables are available and taken for a particular day.

For example I am unsure of how to see how many tables are available for August, 10th, 2003.

Any ideas?

Thanks,
Jason

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 10:37:15

Did you ever determine what interval of time between reservations are acceptable...is it different per restr.?

TALLY HO

I would think you need a table of all available time, and then join the tables to it where the res time is not in it...

You could even add the restr. id to it to customize the times per restr.

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 10:44:35

You need another table of Days and add that to the cross join ...

note that to replace tables of Days or Times, you can just create a generic table of "Numbers" and use that. The Numbers table usually contains about 1000 values (you can always cross join to make more) and it VERY useful in many types of querys.

if you have the table of numbers, to generate days from @d1 to @d2, you can do something like:

SELECT dateadd(dd, Number - 1, @d1) as Day
FROM
Numbers
WHERE Number <= DateDiff(dd,@d1,@d2)

to generate all times in 15 minute intervals, you can something like:

select dateadd(mm, (Number-1) * 15, 0) as Time
FROM
Numbers
WHERE Number < (96)

WARNING: Neither of the above is tested, both probably need some tweaking (i.e.,I may have reversed some function arguments or something like that). but you get the idea.

regardless of how you generate the Times or the Days, my query becomes something like:

select A.resturantID,
B.TableID,
d.Day,
c.Time
from
Restaraunts A
INNER JOIN
Tables B
on
a.restaruantID = b.restaurantID
INNER JOIN
Times C
ON
C.Time between OpenTime and CloseTime
CROSS JOIN
Days D
ON D between @StartDate and @EndDate

you get the idea ....

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-05 : 10:58:32

I was always curious about this in SQL Server...

A join without a relational predicate between tables is always a cartesian product...

So why the need for the CROSS JOIN operator? Does hold any specific meaning...it's like coding LEFT OUTER JOIN, instead of LEFT JOIN, no?


USE Northwind
GO

CREATE TABLE myTable99 (col1 int)
GO

INSERT INTO myTable99 (col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
GO

CREATE TABLE myTable00 (col1 int)
GO

INSERT INTO myTable00 (col1)
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
GO



SELECT a.col1, b.col1
FROM myTable00 a, myTable99 b

DROP TABLE myTable99
GO

DROP TABLE myTable00
GO

Brett

8-)

SELECT POST=NewId()

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 11:10:30

It's a good way to make sure it is known you intend to do a cartesian product and it isn't happening accidentally.

it also follows the ANSI join syntax convention nicely, in that it is explicily listing how the tables relate.

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-05 : 11:37:48

Jeff,

You're far to harsh on Brett.

...and me..... accusing me of loving CHAD!!!

----------------
Shadow to Light

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 09:24:57

quote:
Originally posted by Amethystium

Jeff,

You're far to harsh on Brett.

...and me..... accusing me of loving CHAD!!!

----------------
Shadow to Light

Amethystium,

Are you kidding?

I count on Jeff..better here than on the job...

[homer]
OK, But why two tables then?
[/homer]

It's a cold (IT) world out there...

This whole site is about getting better...doesn't just have to be about SQL though...can be about being sharp...and making sure you READ things (unlike me) more carefully...sure to get lashed in the office if you don't..too easy here to miss if you're not hands on with stuff...

Still no excuses..because there aren't any...

Brett

8-)

SELECT POST=NewId()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources