Posted by --CELKO-- on 01/17/07 19:00

>> 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?

[Back to original message]