Re: Square pegs in round holes

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 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

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 #Buckets $/bucket
> MaterialCost
> 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 - 00:04:28 CEST