Re: Square pegs in round holes
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
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