Optimised Design - Index or Not?

[Keith Christmas]

I have fully normalised a data model for an Employment Agency database which
I would now like to implement using Access 2000 (which I am not that
familiar with).

Within the logical data structure I have two tables; "Order" (Primary Key =
Order Nbr) and Order Line (PK = Order Nbr & Line Nbr). Each day there are
possibly 500 orders each with an average of 5.

Each Order Line represents the Temp whose service is required at a specific
Date and Time (These date/times are usually 1 to 5 days after the order is
placed). The Temp Nbr is stored on the Order Line and acts as a foreign key
to the 'Temp' table.

My requirement is to search the database and find a Temp that has not been
assigned a job on that day, so...

Option a)
Attach an index on the Order Line record assigned to the 'Temp Nbr' foreign
key and to the Date Required attribute and then create a query that searches
each Order Line looking for Temps that have NOT been assigned to an order on
that date by checking for Null values.

I have a nasty feeling that this option will need to check EVERY order line
on EVERY ORDER that has been placed for a future date in order to determine
if the requested Temp is available or not on that specific date.

or

Option b)
Create separate entity (called Booking Detail for sake of argument) with
Temp Nbr and Booked Date as the PK. This table will be written to each time
a Temp is assigned to an order line. Now all I need to do to determine if
the Temp is free is access the Booking Detail record. If an error occurs
(i.e. record not found) then I know that the Temp is free and I haven't had
to trawl through all the Order Lines.


or

Option c)
something else???

Any expert guidance into the most efficient method of doing this would be
very welcome,

I hope I've made this clear, and I hope this is the correct news group to
post such a question.
TIA,

Keith Christmas

 

[John Vinson]

Attach an index on the Order Line record assigned to the 'Temp Nbr' foreign
key and to the Date Required attribute and then create a query that searches
each Order Line looking for Temps that have NOT been assigned to an order on
that date by checking for Null values.

I have a nasty feeling that this option will need to check EVERY order line
on EVERY ORDER that has been placed for a future date in order to determine
if the requested Temp is available or not on that specific date.

A "frustrated outer join" query should be reasonably efficient here.
Join the OrderLine table to the table of temps, with an Outer Join to
show all temps and matching OrderLines; and use a criterion of IS NULL
on the Temp Nbr in OrderLines.

 

[Keith Christmas]

Thanks for the Response John,

I'd more or less come to the same conclusion myself since there would be a
1:1 relationship between Order Line and Booking Detail meaning that they
ought to be combined.

I've been studying the Access 97 Developers Handbook (Litwin et al) and the
advice given is
"Avoid using outer joins if possible because they require a complete scan of
the entire preserved table (that is, the 'Left' table in a left outer
join)".
Other than your suggestion though, I don't see another way of doing it.

So... Am I better using the Temp Nbr and Date as a foreign key in Order Line
*and then indexing on those fields*? These will provide a way of uniquely
identifying the Order Line from the Temp perspective. If I do this, will
Rushmore optimisation be used?

thanks again for the feedback,
Keith

 

[John Vinson]

So... Am I better using the Temp Nbr and Date as a foreign key in Order Line
*and then indexing on those fields*? These will provide a way of uniquely
identifying the Order Line from the Temp perspective. If I do this, will
Rushmore optimisation be used?

I'd expect so, but I don't know much about the internals of how the
query optimizer works.