Hi all
I'm in the process of upgrading a job from .mdb to .adp->msde & am
reconsidering some aspects of the design of me schema while I'm at it.
One of the tasks of this job is to schedule the loading and unloading of
trucks. For this the scheduler needs to see the load on the truck, before
and after loading, i.e. Opening Load + Variation To Load = Resulting Load.
The relation ship between these figures and other attributes form the
basis of a wide range of reporting out comes.
To keep reporting efficient, as well as making the front end easy to
program- where the results of the changes need to seen dynamically. I have
all 3 of these fields in the table (i.e. opening load, variation and
resulting load). I've also been aware from the start that this isn't
'good' relational design, as the opening load, and resulting load fields
contain redundant data (i.e. opening load is the sum of variations to the
previous record, and resulting load is the open + variation).
Having these redundant fields causes an overhead in terms of maintenance
and design (i.e - I always have to be aware to ripple through an changes
in these fields), but the performance overhead at run time seems less than
removing them and building a record set that has these sums (on a
continuous form) constantly re-evaluated.
My question after all of this is what would all you sql gurus do - go with
redundant fields, or is it possible to keep the design text book and keep
these derived fields derived and dynamic as part of the record source?
Looking forward to any discussion on this topic
Thanks
Glenn