|
|
Posted by David Portas on 01/16/07 22:30
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|