Nested Equipment Sets and premature optimization

One of two the optimizations I'm implementing (purely based on intuition) is a table of nested equipment sets using the "better_nested_sets" plugin for rails by Jean-Christophe Michel.

This first of two optimizations is intended to speed up (or make easier) the checks for conflicts and equipment availability by quickly loading a collection of required equipment based on an equipment package.

With a single simple query I can get a collection of the required equipment from any equipment package. The alternative is to recursively crawl the tree to build the same collection. Sweet!

How does this magic work? BNS crawls the master equipment package tree (the 'equipments' table) and stores each parent in the 'equipment_nest' table as separate trees, then nests all their required children in their tree. (There is an excellent description of nested set theory and the BNS plugin on the web.) On demand or via trigger events, the BNS table can be totally deleted and rebuilt based on the master equipment tree information.

The second of the two optimizations will also speed up checks for conflicts and equipment availability by quickly loading a collection of equipment that is out between a pair of dates/times.

It's a simple table of equipment reservations. Just straight records of equipment with earliest date/time out and latest date/time in. Can't think of any optimizations for this table or clever use of structure. Can't index the date/time columns because I'm having to search on both so there's no way to index them efficiently. Rats. But at least this table will be small and compact. It can be rebuilt on demand or trigger event and records can be removed whenever equipment comes back in.

Notes on my implementation of the 'EquipmentNest' model:

Since there are multiple trees in the the nests (how ironic) I'll be using the :scope option to separate the trees into equipment packages (the parent equipment trees). So each equipment package is a tree and is represented by a nested set of required children equipment.

I will be adding only the child leaves of the parent. So I'll skip equipment packages and go straight to the physical equipment items. For instance, the "shoot a film in a weekend" package that contains a camera package, sound package and lighting package will have JUST the equipment items that make up this large package: the camera body, XLR cables, boom pole, shockmount, lighting fixture, scrims, light stands, power cords, microphone, wireless receiver beltpack, wireless transmitter beltpack, etc.

I'll be using all the defaults for the BNS options but will enrich the model with minimal data from the equipment edge: a reference to the child equipment, the required quantity plus a reference to the parent equipment (which doubles as the scope).