Square pegs in round holes

From: Graham Bellamy <dontwriteme_at_ask.first.com>
Date: Sat, 18 May 2002 04:00:31 +1000
Message-ID: <ac3gbf$82q$1_at_perki.connect.com.au>

I'm after some tricks of the trade or work-arounds re this question: How do you go about trying to create a normalized set of tables when there are variations
between sets of information that would otherwise be fairly standard?

I'm a complete newbie in comparison to the level of some of the stuff you guys have been posting here, but I'm at least familiar with 1, 2 & 3NF & parent/child relationships - basic db design. I'm using Access97, and have no experience with other db's.

Ok, that's my question in a nutshell (if you can understand it), but here's what I'm talking about more specifically...

• Client does floor surfacing (eg. non-slip resin treatment on top of existing concrete)
• Must make Quote forms that calculate costs & resin quantities, then reports etc.
• Some information is consistent regardless of treatment type (Client details, job description, Floor Area)
• Depending on treatment type, the calculation of resin quantities changes - and there 'starts' my problem.

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

Some NEVER have sealers......etc etc

Then, say, a Pigment Qty might depend on sum of Qty of Primer + Resin + first (of potentially many) sealer, but not sand amount. All these different treatment types require different fields. This also doesn't allow me to use expressions and apply them to all treatment types.

Do you get the point?

It annoys me that they are all very similar in general (rates, Qty's etc), but each with their own little Treatment-Specific idiosyncracies.

The 'utopian' situation would be to have all these forms stored in one set of related tables, because I want to report on Job totals/month or costs by sales rep etc. If I make 7 different quote forms, I don't know how I can query this in its entirity, only if I limit reporting to the type of treatment.

Surely this is a common situation that programmers have to work around. Also, if I have to make 7 individualised tables/forms/reports etc, the client won't be able to add new treatment types when I'm gone, without needing a programmer each time.

Without knowing any better way, I've started to design my db in what I know is the wrong manner.
If anyone's interested in seeing what I've done already (I like to call it the christmas tree effect - one trunk and loads of branches), I can send the zipped db (200kb).

TIA (sorry about the length)
Graham Received on Fri May 17 2002 - 20:00:31 CEST