Re: Square pegs in round holes

From: Graham Bellamy <dontwriteme_at_ask.first.com>
Date: Sun, 19 May 2002 03:19:03 +1000
Message-ID: <ac629i$a50$1_at_perki.connect.com.au>


"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0205171404.5275cee6_at_posting.google.com...
> Nice continuation of some earlier thread.
>
> Don't store them the same way the materials are labeled, choose
> canonical units and transform anything to them. You might resist a to
> drop "buckets" from your database schema design, but you don't have a
> better choice. It is a job of your application to convert everything
> into your canonic units, and it's the application job again to present
> it back to the customer in the form he wants it to see.

So because Sand comes in bags, and Resin comes in 'Kits' (sometimes) and Primer could sometimes just be ordered by the liter, I would have to label the 'BktSize' column you show below as 'ContainerSize' or something? To show any units that vary b/w Materials, I would have to store them in another table, right?

> So a case
> statement in your application program is inevitable, but at least the
> database schema is as simple as it could be.
>
> The "normalized design" for your schema is:
>
> Type Thickness Rate(Sqm*mm/L) BktSize(L) TotalVolume(L)
> Cost($/L)
> Primer 1mm 0.99 20 40 0.50
> Resin N/A 0.85 15 45 0.34
> Sand N/A 0.73 N/A 22 0.22

Right, so that is telling me that it's ok to store repeated null data, which is not what I know as being normalized. Is this what people mean when they talk about de-normalization?

For convenience of standardising calculations, should I force the user to enter Rate in a certain format (eg. L/Sq.m instead of Sq.m/L - user uses both, depending on how it's written on the bottle). I've read it's not a good idea to make the user conform for my benefit.

Some treatments require a pigment to be added. The calculation of this is dependent upon Qty of other materials, which again changes from product to product. Examples of this:

Rate * (Total Qty of liquids [Primer + Resin + Sealers])
Rate * (Total Qty of Sealers only)
Rate * (Total Qty of Resin & Sealers [not Primer])

Keeping in mind that the client should be able to add new floor treatments, what info should be stored such that Qtys can be calculated without need for additional hard code?

Eg. Epoxy Screed requires Pigment, Qty=5% of ResinQty + 2% of all other liquid Qtys

Would I be close with:

tblTreatmentSpecs
tsCode Treatment Type ..... Requires Pigment

001    Epoxy Screed                Yes
002    Epoxy Rollcoat               No

tblMaterialSpecs   (so form can show user what units the values are in)
tsCode  Matl      RateUnit          QtyUnit    etc....
001       Primer    Sq.m/L                L
001       Resin      Sq.m/kg/mm        kg

tblPrimerSpecs
tsCode Material Rate#

001      Primer        1
001      Resins        2
001      Sealers       1

Then somehow allow user to enter the rates in a form, and come up with some code to figure out which materials to sum.

Man, I can see headache Case statements coming up. Though I'd be really screwed if eg. calc depended on all sealers except the first.

I think I'm going too deep with this. The client wanted a (relatively) quick db to be able to do his calcs quickly for quoting purposes, and if possible to come up with some Cost reports. But I can't think of a simple way to do this without restricting his ability to add new treatments after I'm gone.

Graham

> You might also add a density column if you want translating volume
> into weight (sorry, mass;-).
>
> Question: why Rate is close to 1, but always less than 1? Answer: it
> essentially determines the effectiveness of a material, describing how
> much of the product is waisted during the job.
>
> Of course, the obvous drawback is that one have to calculate Rate and
> Thickness even if you do wallpaper decorating. However, we don't even
> have to be presize: any realistic Thickness number would do and the
> Rate can be set just to 1. Then when you want to enter, say, 20 Sqm of
> wall paper into the database, just calculate the volume!
>
> The nice thing about physical units is that they are consistent and
> orthogonal. You can leverage it in real-life application.
>
> "Graham Bellamy" <dontwriteme_at_ask.first.com> wrote in message
news:<ac3gbf$82q$1_at_perki.connect.com.au>...
> > There are about 7 treatments (at present) that each have their own
little
> > calculation differences.
> > Some resin application rates are to be stored in kg/sq.m, so
Qty=Rate*Area
> > Some in sq.m/L, so Qty=Rate/Area
> > Some depend on thickness, and are in L/sq.m/mm, so
Qty=Rate*Area*Thickness
> > Then the hard bit:
> > Some could require Only 1 primer, Only 1 resin, Always 2 sands, but many
> > layers of Sealer.
> > Most come in buckets, so #buckets=Qty/BucketSize, so columns look like:
> > (Area=100, Thickness=4)
> > Type Rate Qty BktSize #Bkts $/bucket
MatlCost
> > Primer 5Sqm/L 20 18 2 75
150
> > Resin 0.2kg/Sq.m/mm 80 20 4 130
520
> > (Then many layers of sealer, so maybe another table, 1-many)
> > Sealer1
> > Sealer2.......
> > Then sand, which doesn't come in buckets, just the kilo amount:
> > Sand 1 0.1kg/Sq.m/mm 40 5 200
> > Sand 2 etc
> >
Received on Sat May 18 2002 - 19:19:03 CEST

Original text of this message