Re: Square pegs in round holes

Mikito emailed me the suggestion below. In the example data, where there are references like C7, these are references to cells in some XL sheets I made up as correctly functioning example calcs.

Mikito, thankyou for the thought you've put into this. Could you please give a little explanation of the example data you provided, as I don't understand some of it, particularly things like Volume entries in the tables, which seem to be calculated (and so they should be), yet calculated values should not be stored in a table. Surely this should be done by queries. Or are these the values that would get finally stored after my code changes the values into canonical units? In which case I can understand the cost being changed, but not the volume (which equates to Quantity, right?).

• Forwarded message --- I would suggest the following db design:

table Materials (
  name varchar2 primary key,
  porousness number default 1, -- [L/L]
  thickness number, -- [mm]
  density number, -- [kg/L]
  cost number -- [$/L]
)

table Treatments (
  id integer primary key,
  name varchar2,
  area number, -- [M*M]
)

table TreatmentComponents (
  treatment_id integer foreign key refers Treatments(id),
  component_num integer,
  volume number -- [L]
)

Let's consider what do you store in Epoxy Screed case:

insert into treatments (id, name, area)
values (1, 'Epoxy Screed', B7);

insert into materials values (name, porousness, thickness, density, cost)
values ('Primer',1,1/C17,null,E14)

insert into materials values (name, porousness, thickness, density, cost)
values ('Resin',1,1,2.1,E15/2.1)

insert into TreatmentComponents values (treatment_id, conponent_id, volume)
values (1,1, B7/C17)

insert into TreatmentComponents values (treatment_id, conponent_id, volume)
values ( 1,2, B7/(1+B9) )

My schema doesn't contain the data that would allow to restore the spreadsheet with original units yet, I'm thinking about it. Received on Wed May 22 2002 - 22:46:48 CEST