Determining double-booking conflicts in star-join relationship

Very thankful in advance to anyone who can give me a tip on the right
direction to go here.

Table 1: Client
Table 2: Show (an entertainment production)
Table 3: Site (a certain location at a certain date... a one-day
competition)

(Each table uses unique primary keys for all relationships.)

Table 4: Order

A Client places an Order for a certain single Show which the Client
will perform at numerous Sites.

Table 5: Reservation (a join-table for the many-to-many relationship
between Orders and Sites)

When placing an Order, the Client provides a series of Sites where
(and when) they'll be performing the Show they are ordering. Each of
the Sites is entered into the Order, and a Reservation for the Show is
created for each Site.

The problem: we need to maintain exclusivity. We cannot have two
different Clients perform the same Show at the same Site. (In other
words, each Reservation--combination of Show and Site--must be
unique.)

The bigger problem: A self-join relationship in Reservations could
easily find and flag such duplicates as they occurred, but it won't
work since the needed calculations in the Reservations table must
reference related data. Therefore, those calculations can't be stored/
indexed, therefore those calculations can't be used in a self-join
relationship. (Please let me know if I'm wrong... I hope I am.)

So, the calc in Reservations could be: cShowAtSite = Show::Key &
Site::Key
And the relationship match fields could be: cShowAtSite =
cShowAtSite

But... it won't work. I'm guessing it's because the Order is acting as
a star-join table between Client, Show and Reservation... therefore
Reservation can't get to Show without going through Order. Am I
looking at that part correctly?

Right now, I'm adding a "uniqueness check" to the ordering script, but
that's not a complete solution. Various changes can occur to Sites and
Shows after the Order is placed... so checking only at the time of
ordering only helps somewhat.

I wouldn't be surprised if I've thought myself into a corner on
this... and I'll be very glad if someone can give me a "that should
have been obvious" tip.

Thanks again!
-Rob Steward

Re: Determining double-booking conflicts in star-join relationship

In article
,
robdsteward@gmail.com wrote:

> Very thankful in advance to anyone who can give me a tip on the right
> direction to go here.
>
> Table 1: Client
> Table 2: Show (an entertainment production)
> Table 3: Site (a certain location at a certain date... a one-day
> competition)
>
> (Each table uses unique primary keys for all relationships.)
>
> Table 4: Order
>
> A Client places an Order for a certain single Show which the Client
> will perform at numerous Sites.
>
> Table 5: Reservation (a join-table for the many-to-many relationship
> between Orders and Sites)
>
> When placing an Order, the Client provides a series of Sites where
> (and when) they'll be performing the Show they are ordering. Each of
> the Sites is entered into the Order, and a Reservation for the Show is
> created for each Site.
>
> The problem: we need to maintain exclusivity. We cannot have two
> different Clients perform the same Show at the same Site. (In other
> words, each Reservation--combination of Show and Site--must be
> unique.)
>
> The bigger problem: A self-join relationship in Reservations could
> easily find and flag such duplicates as they occurred, but it won't
> work since the needed calculations in the Reservations table must
> reference related data. Therefore, those calculations can't be stored/
> indexed, therefore those calculations can't be used in a self-join
> relationship. (Please let me know if I'm wrong... I hope I am.)
>
> So, the calc in Reservations could be: cShowAtSite = Show::Key &
> Site::Key
> And the relationship match fields could be: cShowAtSite =
> cShowAtSite
>
> But... it won't work. I'm guessing it's because the Order is acting as
> a star-join table between Client, Show and Reservation... therefore
> Reservation can't get to Show without going through Order. Am I
> looking at that part correctly?
>
> Right now, I'm adding a "uniqueness check" to the ordering script, but
> that's not a complete solution. Various changes can occur to Sites and
> Shows after the Order is placed... so checking only at the time of
> ordering only helps somewhat.
>
> I wouldn't be surprised if I've thought myself into a corner on
> this... and I'll be very glad if someone can give me a "that should
> have been obvious" tip.
>
> Thanks again!
> -Rob Steward

From your word description, it seems you have the following
relationships:

Client::kpClientID = Order::kfClientID
Show::kpShowID = Order::kfShowID

Order::kpOrderID = Reservation::kfOrderID
Site::kpSiteID = Reservation::kfSiteID

The key fields of the relationships should all number fields:
Primary key for each table an automatically-generated and therefore
unique serial number in that table;
Foreign key filled by creation from the Parent table or by script when
you create the Child record.

You can add a number field to Reservation to hold the ID of the Client,
call it kfClientID. It is not used directly in the relationships you
described, but could be used for the self-join. It could be filled by
lookup or by script when the Reservation record is created.
Likewise you could add a number field for ShowID in the reservation
table, call it kfShowID, again filled by lookup or script when the
record s created.

Now you have all the key fields you need in the Reservation table to do
the self-join check for uniqueness. All of them are number fields with
values stored locally in the Reservation table. They were filled on
creation from the related tables, but now they are simply locally-stored
numbers, so the self-join will work.

Re: Determining double-booking conflicts in star-join relationship

On Nov 18, 7:04 pm, Bill wrote:

> From your word description, it seems you have the following
> relationships:
>
> Client::kpClientID = Order::kfClientID
> Show::kpShowID = Order::kfShowID
>
> Order::kpOrderID = Reservation::kfOrderID
> Site::kpSiteID = Reservation::kfSiteID
>
> The key fields of the relationships should all number fields:
> Primary key for each table an automatically-generated and therefore
> unique serial number in that table;
> Foreign key filled by creation from the Parent table or by script when
> you create the Child record.
>
> You can add a number field to Reservation to hold the ID of the Client,
> call it kfClientID. It is not used directly in the relationships you
> described, but could be used for the self-join. It could be filled by
> lookup or by script when the Reservation record is created.
> Likewise you could add a number field for ShowID in the reservation
> table, call it kfShowID, again filled by lookup or script when the
> record s created.
>
> Now you have all the key fields you need in the Reservation table to do
> the self-join check for uniqueness. All of them are number fields with
> values stored locally in the Reservation table. They were filled on
> creation from the related tables, but now they are simply locally-stored
> numbers, so the self-join will work.

Thanks for the ideas. The main problem I found with manually setting
the "missing key" like that (via lookup or script) is that if anything
changed later--like the Client changing their Order to a different
Show--then the Reservation self-join would be inaccurate.

I believe I have solved the problems, though it was a bit of a pain.
Since Reservations was what I wanted to self-join to check for
uniqueness, and since a Show key and a Site key were both necessary to
do that self-join, I reworked the relationships to make Reservations
the star-join table between Order, Show and Site. (So now Client to
Order is a simple one-to-many... not part of the star-join.)

Reworking the relationships, scripts and portals which relied on the
previous strategy was a bit of a pain.

For the benefit of anyone searching Usenet in the future, here's the
rule of thumb: If you want to keep a "live list" of double-bookings,
make sure your join-table has direct access (not related access) to
the keys you'll be comparing.

Thanks again!
-Rob Steward