Strategy for Defining Fields

[Jarvis]

I have a question about calculation fields that could be a pretty important one to others as well. It addresses strategy when building larger databases.

I'm in the process of building a comprehensive solution for calculating math for all the products my cabinet shop builds. It also relies heavily on the use of graphics. (There could easily be 100 different views of the data between various layouts and tabbed panels.)

There are a lot of calculations. Most of them are simple arithmetic.

Someone today gave me an idea that could change my whole approach. I need some advice on this idea.

Rather than building this database with a million calculations, would it be more efficient to radically cut back on the number of calculated fields and instead make more use of $variables?

There are a bazillion calculations such as:

[color:red]Cabinet floor = box width - side thickness - side thickness.

[color:blue]Door Panel = door width - stile - stile

Could I express all of these calculations through one Case statement and have the arguments for each calculation be pulled as a variable instead?

If a file had a lot of records, would using variables reduce processing time? Would variables be more efficient than calculations for this purpose?

Thanks,,

Jarvis

[Søren Dyhr]

First issue here is, do not regard a database as a a spreadsheet - this would perhaps mean that each abstract component should be broken out into a separate table, and each type of cabinet should be a join table record gathering the items.

However would the variable length of ties/connections suggest you read up on recursive structures:

https://www.jonathanstark.com/recursive_data_structures.php

--sd

[comment]

It is difficult to judge efficiency without seeing the exact implementations, but I will venture this:

1. I don't think a Case() function having to choose one out of "bazilion" formulas will be any faster than a relationship - probably slower.

2. Which formula needs to be used with which product is DATA, and it should be kept in records (where, if necessary, it can be modified by user), rather than in a calculation where only the developer has access to it.

[Jarvis]

Thanks guys.

You have helped me again.

I have so far been pretty attentive to keeping data separate from calculations.

The customer data (type of cabinet selected, overall width etc) is in one table.

The calculations I use to develop cabinet math is another table.

The parameters (arbitrary design considerations) are in a third table.

This way I can separate one customer from another and I can go in and change the rules (parameters) in one specific location.