Correct Schema for this business problem?

[Blast]

I need help modeling schema for a particular issue that i've never run
across before. And for the life of me, I cannot figure out how to model
it - at least in a way that feels correct. Please let me know if I need
to post this else where.

Here are the tables:

Reservation <------------Customer_Reservation <--------->Customers

GolfCourses <--------------Customer_Golfing
TeeTimes <------------------Customer_TeeTime
Ok so here is the business rules. A reservation will be assigned one or
more customers. A reservation has one or more golfcourses. Golfcourses
will be assigned people. These people can only come from those people
assigned to the reservation that the golfcourse belongs to. GolfCourses
have one or more teetimes. TeeTimes will be assigned people. Those
people can come from only the people assigned to the golfcourse that
the teetime belongs to.

Basically, how can I setup the schema to enforce the business rules? I
can see only to do this at the application level. Maybe I have modeled
this incorrectly?

[David Portas]

Blast wrote:

I need help modeling schema for a particular issue that i've never run
across before. And for the life of me, I cannot figure out how to model
it - at least in a way that feels correct. Please let me know if I need
to post this else where.

Here are the tables:

Reservation <------------Customer_Reservation <--------->Customers

GolfCourses <--------------Customer_Golfing
TeeTimes <------------------Customer_TeeTime
Ok so here is the business rules. A reservation will be assigned one or
more customers. A reservation has one or more golfcourses. Golfcourses
will be assigned people. These people can only come from those people
assigned to the reservation that the golfcourse belongs to. GolfCourses
have one or more teetimes. TeeTimes will be assigned people. Those
people can come from only the people assigned to the golfcourse that
the teetime belongs to.

Basically, how can I setup the schema to enforce the business rules? I
can see only to do this at the application level. Maybe I have modeled
this incorrectly?

You didn't specify any keys so this is pure speculation:

CREATE TABLE CustomerReservations
(CustomerNo INT NOT NULL REFERENCES Customers (CustomerNo),
ReservationNo INT NOT NULL REFERENCES Reservations (ReservationNo),
PRIMARY KEY (CustomerNo,ReservationNo));

CREATE TABLE CourseReservations
(CourseNo INT NOT NULL REFERENCES Courses (CourseNo),
ReservationNo INT NOT NULL REFERENCES Reservations (ReservationNo),
PRIMARY KEY (CourseNo,ReservationNo));

CREATE TABLE CustomerCourseReservations
(CustomerNo INT NOT NULL,
CourseNo INT NOT NULL,
ReservationNo INT NOT NULL,
FOREIGN KEY (CourseNo,ReservationNo) REFERENCES CourseReservations,
FOREIGN KEY (CustomerNo,ReservationNo) REFERENCES
CustomerReservations);

.... etc

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Alex Kuznetsov]

Blast wrote:

I need help modeling schema for a particular issue that i've never run
across before. And for the life of me, I cannot figure out how to model
it - at least in a way that feels correct. Please let me know if I need
to post this else where.

Here are the tables:

Reservation <------------Customer_Reservation <--------->Customers

GolfCourses <--------------Customer_Golfing
TeeTimes <------------------Customer_TeeTime
Ok so here is the business rules. A reservation will be assigned one or
more customers. A reservation has one or more golfcourses. Golfcourses
will be assigned people. These people can only come from those people
assigned to the reservation that the golfcourse belongs to. GolfCourses
have one or more teetimes. TeeTimes will be assigned people. Those
people can come from only the people assigned to the golfcourse that
the teetime belongs to.

Basically, how can I setup the schema to enforce the business rules? I
can see only to do this at the application level. Maybe I have modeled
this incorrectly?

You might get more help after you have posted your CREATE TABLE
statements. Make sure your constraints are included.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[Blast]

Ok, i drew out your diagram and my design thoughts are exactly like
yours. And I was hoping it wouldn't be.
Let me explain. I'm using the windows form (.net) for my UI and there
are no controls that recognize this pattern, at least not right out of
the box (not to my knowledge).

For example, for the CourseReservations I want to have a comboBox that
lists all Customers in the reservation (CusomterReservations). Then
next to the customers name would be a checkbox, which would represent
existance of data entered into the CustomerCourseReservation. When you
toggle on and off the checkbox it would delete or add the entry into
the CustomerCourseReservations.

So. thats what I need to do keeping this schema. I was hoping I had
incorrectly created the schema and there was an easier way to model the
problem.

Thanks for your reply.

[Blast]

http://www.condoresorts.com/problem.jpg

Hows that?

[Blast]

http://www.condoresorts.com/Problem.jpg

With a capital P

[David Portas]

Blast wrote:

Ok, i drew out your diagram and my design thoughts are exactly like
yours. And I was hoping it wouldn't be.
Let me explain. I'm using the windows form (.net) for my UI and there
are no controls that recognize this pattern, at least not right out of
the box (not to my knowledge).

Obviously the UI code has nothing to do with constructing an effective
data model. The right solutions rarely come "out of the box".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[--CELKO--]

>I need help modeling schema for a particular issue that i've never run across before. <<

I looked at your ER diagram. A good rule of thumb is that when it has
a cycle in it, you are in trouble. You also defined a lot of MVDs
which guarantee 5NF problems. You will also have what Tom Johnston
calls non-normal form redundancies. And the golf courses never appears
in the diagram.

I also had problems with your business rules:

> A reservation will be assigned one or more customers.

A reservation has one or more golfcourses. <<

Shouldn't a reservation put a group of cusotmers onto the SAME golf
course? You allow 5 poeple to play as a reservation on 10 different
courses.

>Golfcourses will be assigned people. <<

Shouldn't that be the other way around? So I do not need a reservation
at all because I have my own course?

>These people can only come from those people assigned to the reservation that the golfcourse belongs to. <<

Very convoluted and inside out.

>GolfCourses have one or more teetimes. <<

No, a golf course has 18 holes with a par for each hole, etc. A
reservation is an event and events have times; a golf course is an
eneity and they have existence.

>TeeTimes will be assigned people. <<

So everyone in a resrevation can tee off at a different time, as well
as not beingon the same course?

>Those people can come from only the people assigned to the golfcourse that the teetime belongs to.<<

Very convoluted and inside out again.

>Maybe I have modeled this incorrectly? <<

Yes. Let's try this:

A) Each reservation has
1) a single golf course - manditory
2) a single tee time - manditory
3) one or more customers - manditory
4) Reservations are identified by course and tee time

CREATE TABLE Customers
(customer_id INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(25) NOT NULL,
etc);

CREATE TABLE Golfcourses
(golfcourse_id CHAR(5) NOT NULL PRIMARY KEY,
golfcourse_name VARCHAR(25) NOT NULL,
etc.);

CREATE TABLE Parties
(party_name CHAR(25) NOT NULL PRIMARY KEY
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id)
ON UPDATE CASACDE
ON DELETE CASACDE,
etc.);

CREATE TABLE Reservations
(golfcourse_id CHAR(5) NOT NULL
REFERENCES Golfcourses (golfcourse_id)
tee_time DATETIME NOT NULL,
party_name CHAR(25) NOT NULL
REFERENCES Parties (party_name)
ON UPDATE CASACDE
ON DELETE CASACDE,
PRIMARY KEY (golfcourse_id, tee_time));

A) I would think about getting the count of golfers ahead of your
current reservation and using a look-up table to compute the tee time
since you expect a larger party to play slower than a small one. You
can refine the estimates with actual data later.

Old joke:
wife to husband coming home from golf game: "You're back late! You go
out drinking?"
husband: "No. Eddie had a massive heart attack on the third hole."
wife: "I'm soory! That must have been awful!"
husband: "It was! What with hitting the ball and dragging the body, I
thought I'd never finish before sundown."

B) You will have to create a customer first, so I would allow for his
guests in the customer id scheme, thus:
12300 = 'Bobby Jones"
12301 = 'Bobby Jones - guest #1"
etc.
12312 = 'Bobby Jones - guest #12" or whatever the guest limit is

C) Since people tend to play with a regular group of friends (or
enemies), you build the Parties table entry next and retain them for
re-use. A single player is a party of one. You will need dummy party
identifiers to construct any ad hoc situations that might come up.

Does this do what you wanted?

[Blast]

Well thanks for the reply. BUT. There is a reason why we want things
done the way we want things done. Let me explain.

I looked at your ER diagram. A good rule of thumb is that when it has
a cycle in it, you are in trouble. You also defined a lot of MVDs
which guarantee 5NF problems. You will also have what Tom Johnston
calls non-normal form redundancies. And the golf courses never appears
in the diagram.

What are MVD's? We need the cycle. The problem is, we don't book the
golf directly. We're not a golf course. People who are in a reservation
don't always play golf. So, we assign people to a reservation. Some of
those people in the reservation might be playing golf, but they must be
in the reservation to play golf. Also, the tee times need be confirmed.
This isn't automatically done. So, we want to make sure the people
assigned to the golf course have tee times made. If they have no tee
times, then we need to be able to query that information becuase
someone needs to call the golf course and make the tee times.

A reservation will be assigned one or more customers.

A reservation has one or more golfcourses. <<

No, it has 0 or more.

Shouldn't a reservation put a group of cusotmers onto the SAME golf
course? You allow 5 poeple to play as a reservation on 10 different
courses.

Yeah, some people in the reservation will be playing golf, some won't.
2 people in the reservation might be playing 10 different golf courses.

Golfcourses will be assigned people. <<

Shouldn't that be the other way around? So I do not need a reservation
at all because I have my own course?

Yeah, we don't have our own course.

These people can only come from those people assigned to the reservation that the golfcourse belongs to.

Very convoluted and inside out.<<

Why is this inside out?

GolfCourses have one or more teetimes. <<

No, a golf course has 18 holes with a par for each hole, etc. A
reservation is an event and events have times; a golf course is an
eneity and they have existence.

Tee times are made in groups of 3 or 4 typically. So if you have a big
group, lets say 36 players you need many tee times.

TeeTimes will be assigned people. <<

So everyone in a resrevation can tee off at a different time, as well
as not beingon the same course?

Yes, that's right.

Those people can come from only the people assigned to the golfcourse that the teetime belongs to.<<

Very convoluted and inside out again.

Yes this is right. You see, someone will put people in a reservation.
Weeks later can go by. Then, someone from that group might call in and
say, we want to play 3 different golf courses and we need these tee
times. But we can't give them tee times right away because maybe they
are reserving more then a year in a advance. So, we assign them to the
golf course and eventually make their tee times.
You see, I want to be able to query, how many reservations do we have
with people who aren't playing golf. How many people in reservations do
we have who are playing golf but dont have tee times. How do I answer
these questions with the ER diagram you gave me? I can't!

Let me further explain the business just in case anyone wants to
comment.

People make a reservation with us. We book golf, rental cars, other
activities, and rooms. Some people book and get a room, some people
book and just play golf through us. Some people just play activities
through us etc. So the relationship for these items are zero or more.
Since we don't actually own any of these items we have to first assign
people to the item and then assign them a time in which they are
confirmed for the event. We want to know who's assigned what, and then
who actually has been confirmed by the companies the event time.

So we start off by creating a group of people that belong to the
reservation. Then as they decide what they want to do, we start
assigning them different items. Those items need to have things
confirmed by third party organizations. Does that make sense?