vb/mysql question

[bxgirl]

I am mySQL rookie. These are my tables

Cust Table
-------------------
custID1, fname, lname
custID2, fname, lname
custID3, fname, lname

Package Table
-------------
pckgID1, descr1
pckgID2, descr2
pckgID3, descr3


custPackage Table
------------------------------
custID1, pckgID3, begDate, endDate

Note: the custPackage table identifies which package a customer purchased and how long the package is good for.. usually about 1 month

i.e. begDate = 2005-11-1
endDate = 2005-11-30


reservations
---------------------------------------
resID, custid, serviceDescr, apptBegDate, apptEndDate


Objective: Create CR (v7) report to show packages that were purchased and whether or not there are reservations made for the period the package is in effect.

This report will generate upon the request made on vb6 front-end, with mySQL 4 at the backend.
As it stands, there is no direct link from the Package the customer purchased to the reservations made for that package. Not the most elegant so I must rely then on looking at the begDate and endDate in the custPackage table and find what reservations for that customer falls between these dates.

The challenge is the CR 7 and mySQL 4 do not support stored procedures nor subqueries...which is how I would have gone about this. Anyhow, after some reading, most of this processing will have to occur at the application level. Long story short, I need help formulating the select statements.

This is my thought process so far. Am I in the right direction?

Within the VB application

1. Join the cust, custPackage, and Package tables and place the results in Temp or perm table
2. Join temp or perm table to reservations based on custID and do a date comparison.

Can a temp table get created within the VB application, being that temp tables are only good within the mysql session?

Appreciate any help. Thx.

 

[rac2]

How will you know which package covers the reservation when a customer bought two packages for overlapping periods? Maybe this is unlikely or not interesting possibility.

All of the customer packages will be retrieved by a LEFT JOIN.

SELECT cp.custid, cp.pkgid, r.resid
FROM CustPackage cp
LEFT JOIN Reservations r ON r.custid = cp.custid
      AND r.apptBegDate BETWEEN cp.begdate AND cp.enddate

The customer packages with a reservation covered by part of the package will have a resid; those without will have NULL.

Or, fully covered

SELECT cp.custid, cp.pkgid, r.resid
FROM CustPackage cp
LEFT JOIN Reservations r ON r.custid = cp.custid
      AND r.apptBegDate BETWEEN cp.begdate AND cp.enddate
      AND r.apptEndDate BETWEEN cp.begdate AND cp.enddate

If a customer has one package and two reservations, there could be two rows in the result if both reservatons are within the package period.

If a customer has two packages and one reservation, there could be two rows if the packages both cover the reservation. Or, and this would be bad, no rows if the reservation is coverd on one end by one package and on the other end by the second package.

Two packages and two reservations could yield four rows if the dates all occur within the same period.
If that happens, maybe customer service could follow-up, "We see that you have paid twice."

It is not just inelegant, it is dysfunctional to not have the package identified in the reservation.


I dont see how a temp table could even be used without a procedure. An alternative might be a working table, one that is always there, has an identifier for the reporting task, and deleting rows for that task when finished. Or, with a periodic maintenance process to truncate it.

 

[bxgirl]

Oh I know it's not the best design, but this is what I've inherited.

Definitely looking to improve this.

This is the current setup:

--There are services offered
--There are packages that include one or more of these services that a customer can buy. Usually they are only good for 1 month starting at the beginning of the month and end at the end of the month. Usually no overlapping dates.
--The database is not design to know which services are included in the package. The person taking the order knows this. So for example, if within Package1, there are 3 services offered, the person taking the order has to enter all three services in order to get the total of what the package cost.