Re: Designing database tables for performance?

> ... The fact the items are a hierarchy doesnt help
> - difficult to avoid the 'in' statement without
> knowing how many levels there are in the hierarchy.

Below example with an experimental db (based on set theory and lambda calculus) models two orders. The first order by GM is for a battery which is composed of posts, plates and washer. The second order by Mazda is for an alternator composed of armeteur, bearing, and washer. Samples queries find orders with parts at any level of hierarchy.

(new 'washer 'item)

(new 'battery 'item)
(new 'posts 'item)
(new 'plates 'item)
(set battery item posts)
(set battery item plates)
(set battery item washer)

(new 'alternator 'item)
(new 'armeteur 'item)
(new 'bearings 'item)
(set alternator item armeteur)
(set alternator item bearings)
(set alternator item washer)

(new 'gm 'company)
(new 'mazda 'company)

(new 'customer)

(new 'dateTime)
(new 'orderStat)

(new 'order1 'order)
(set (it) customer gm)
(set+ (it) dateTime '20070101.143500)
(set+ (it) orderStat 'shipped)
(set (it) item battery)

(new 'order2 'order)
(set (it) customer mazda)
(set+ (it) dateTime '20070105.162500)
(set+ (it) orderStat 'shipped)
(set (it) item alternator)

(; Get orders with battery)
(; Gets order1/battery)
(getPath (get order instance *) item battery)

(; Get orders with posts)
(; Gets order1/battery/posts)
(getPath (get order instance *) item posts)

(; Get orders with washer)
(; Gets order1/battery/washer

   and order2/alternator/washer)
(getPath (get order instance *) item washer)

(; Get orders with plates or bearing)
(; Gets order1/battery/plates

   and order2/alternator/bearing)
(getPath (get order instance *)

             item
             (| plates bearings))

To run above script, download dbd.exe from www.dbfordummies.com/download Received on Fri Feb 09 2007 - 21:15:39 CET