I have a price list that is a combination of lumber species, thickness and width and each item's price is a function of these three categories. Its layout derives from a spreadsheet based list but now that I'm working in Filemaker, and as I have been told, it is not normalized and isn't user friendly when I try to develop categories for species, thickness and width for our invoicing database.
So if I was to completely restructure the itemized price list table, what would be the optimum or recommended, normalized format? Would it be smaller tables? Multiple tables, etc?
The terms 4/4, 6/4 etc are an industry description of lumber thickness, by 1/4" increments, so 4/4 lumber is really 1" thick and 6/4 lumber is 1.5" thick. The price of a board also increases the wider it gets with three basic categories, narrow <8" wide, regular 8-12 inch wide and wide which is more than 12" wide. All prices are unitized to board feet (bdft) which is a measure of a board's volume, so to actually price a board, the price per bdft is pulled from the table below and then multiplied by the actual volume of a board and that is the final price of the board. So a 1 inch thick, 6 inch wide, 8 foot long board contains 4 board feet (6*1*8/12) of wood and if it was Ash, it would be multiplied by $4 from the table below, so the price of the board is $16.
"normalized" might be the Item and SpeciesID in one table ('Species') and the "sizes" in another table.
SpeciesID_fk
Size
Price
The "Size" could be a value list to keep it consistent, but it appears you'd be importing to get these anyway?
I think I'd start by importing this table (with no column headers - allow the first "row" (of headers) to be the first record). If you already have these imported like this, let us know?
Then step through each species (scripted loop) and create the related records for each of the other "columns", getting the size and price (along with the SpeciesID as the foreign key).
Beverly