Improving my schema

[Glenn Davy]

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

[rkc]

"Glenn Davy" <vm*****@tpg.co m.au> wrote in message
news:pa******** *************** *****@tpg.com.a u...

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?

You don't need a sql guru you need a relational database design guru.
(Geez, that word even looks stupid. guru. )
I don't know any personally, but my guess is that one, if found, would
tell you to fully normalize your data before worrying about optimizing
your application. Or words to that effect.

[MeadeR]

with the speed of workstations and servers - it has become less and
less a factor of performance in regards to redundant data - unless of
cause you're talking about 100's of complex calculations.

I would keep with the normalized approach and build queries/views in
front of the tables to ensure consistent implementation of any
calculations.

You will save in coding and maintenance - I've seen to many programs
that keep calculated fields where the resultant was other then it
should be - either through bugs or changes in calculations... .

Glenn Davy <vm*****@tpg.co m.au> wrote in message news:<pa******* *************** ******@tpg.com. au>...

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