Re: Square pegs in round holes

"Graham Bellamy" <dontwriteme_at_ask.first.com> wrote in message news:<ac629i$a50$1_at_perki.connect.com.au>...
> "Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message
> > 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?

Right. I updated names for some other columns as well:

Type Thickness(mm) Ratio(Sqm*mm/L) ContainerSize(L) TotalVolume(L) Cost($/L)
Primer 1 0.99 20 40  0.50

> To show
> any units that vary b/w Materials, I would have to store them in another
> table, right?

Yes, you want to keep a reference to original units in order to to be able to present the data to the user in conventional form in your application program. It can even be as simple as just a type code, and your application program would have a switch statement where the appropriate case converts "universal" units into "conventional" ones.

Having all data in uniform units has some added benefits. For example, you'll be able to query if all your materials fit into 14' truck.  

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

Nulls are just a negligible issue here. Stay focused on real problems instead of normalization paranoia. You need to be able get your data model right, first.  

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

There is neither L/Sq.m nor Sq.m/L in my proposed data model. 1 L/Sq.m = 1 mm and that's Thickness. Now, I'm not saying that the user necessarily have to be aware of that. Your application suggests to the end user entering either L/Sq.m, or Sq.m/L and then it translates it to canonical units and stores it.  

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

Do you list pigment in the table of materials as well?

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

In a treatment form a user adds marerials and rates for it. In your datamodel you have many-to-many relationship between Materials and Treatments. So what is the problem?  

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

Lack of standartizaton is a problem. Still, you are lucky as you don't seem to have old imperial system units to deal with. Received on Sun May 19 2002 - 04:40:42 CEST