I have a dilemma... Customer Wants vs. Db Design practices (Or maybe
there is an alternative that I"m not seeing.....?)

Customer travels to sites and wants to see the following on one form for
adding/editing:
For each/one location, all trips (dates) and the 3 types of LaborHrs
for each trip.

EXAMPLE:
-----------------------------------------------------------------------
Location: Chicago

Traveldate PrepHrs OnsiteHrs TravelHrs
----------- -------- ---------- ----------
3/24/03 2 5 2
4/1/03 1 4 3
----------------------------------------------------------------------

So to me, this dictates that a trip would be a multiple of the location
(tblTrip), and hrs would be a multiple for trip (2nd table, tblHrs) with
the Hrs table having at least the following fields:

fld: Hrs
fld: HrType (Prep/Onsite/Travel)
fld: Location
fld: TripID (foreign key to tblTrip).

So I can have the form be bound to tblLocations, and can show all the
travel dates in a subform and then in another subform show the 3 types
of hours for 1 selected Travel Date/Trip...but can't think of how to
show all hours for all trips for 1 location and editable - which is how
they want it (as shown above).
well, that is, I can't think of how to do it while keeping to good
design practices.
Help.....I'm hoping that i'm just not thinking straight....and someone
can open my eyes.......

Thanks

Help us understand the relationships between your data....

It appears as if one location can include many trips (1:M). Can you also
state that "one trip can include many locations" (1:M)? If so, then you
have a many-to-many relationship between locations and trips, which will
require a linking table. Or, is a trip limited to only one location?

Here is a possible design, that includes a many-to-many relationship. My
convention is to use "pk" as a prefix for my primary key fieldnames, and
"fk" as a prefix for my foreign key field names:

tblLocations
pkLocationID
Location

tblTravelDetails
fkLocationID | Combined field primary key
fkTripID |
TravelDate
PrepHrs
OnSiteHrs
TravelHrs

tblTrips
pkTripID
TripDescription
TripStartDate

The two foreign keys in tblTravelDetails can be used as a combined field
primary key.....as long as a single trip does not include a second visit to
the same location. If it does, then add the TravelDate as a third field in
the combined field primary key.

_____________________________________________

"parn" <parn (AT) san (DOT) rr.com> wrote

I have a dilemma... Customer Wants vs. Db Design practices (Or maybe
there is an alternative that I"m not seeing.....?)

Customer travels to sites and wants to see the following on one form for
adding/editing:
For each/one location, all trips (dates) and the 3 types of LaborHrs
for each trip.

EXAMPLE:
-----------------------------------------------------------------------
Location: Chicago

Traveldate PrepHrs OnsiteHrs TravelHrs
----------- -------- ---------- ----------
3/24/03 2 5 2
4/1/03 1 4 3
----------------------------------------------------------------------

So to me, this dictates that a trip would be a multiple of the location
(tblTrip), and hrs would be a multiple for trip (2nd table, tblHrs) with
the Hrs table having at least the following fields:

fld: Hrs
fld: HrType (Prep/Onsite/Travel)
fld: Location
fld: TripID (foreign key to tblTrip).

So I can have the form be bound to tblLocations, and can show all the
travel dates in a subform and then in another subform show the 3 types
of hours for 1 selected Travel Date/Trip...but can't think of how to
show all hours for all trips for 1 location and editable - which is how
they want it (as shown above).
well, that is, I can't think of how to do it while keeping to good
design practices.
Help.....I'm hoping that i'm just not thinking straight....and someone
can open my eyes.......

Thanks