Question About Foreign Keys And Match Tables

[DeX]

I've got a system where the user fills out a form on a website in order to get a quote for a building. Depending on the square footage of the building they inquire about, it will price it with a number of different variables based on this square footage. So I've got a table in my database which has columns for minimum / maximum square footage and a price for each. The final price of the building is calculated using the price of the size which falls in between the proper minimum / maximum square footage:

 

name | minimum | maximum | price

Interior Labour | 0 | 10000 | 2.75

Interior Labour | 10001 | 20000 | 2.45

Interior Labour | 20001 | 30000 | 2.13

Interior Labour | 30001 | 40000 | 1.98

Interior Labour | 40001 | 50000 | 1.86

Exterior Labour | 0 | 15000 | 2.34

Exterior Labour | 15001 | 30000 | 2.04

Exterior Labour | 30001 | 45000 | 1.93

Exterior Labour | 45001 | 50000 | 1.88

 

There are more in the table but this is an example. My first question is if I should have a separate table for each of these options based on square footage or if I should have them all in the same table like this. There are probably 4-5 distinct options like this.

 

My second question is if I should store the values into a table like so when the quote is completed:

 

quote_id | interior_labour_rate | exterior_labour_rate | customer_id

1000 | 2.45 | 2.34 | 25

1001 | 1.86 | 1.88 | 26

 

There are much more columns in this table such as the price of the quote and such. I'm wondering if I should do it like this or if I should have match tables for each individual option of the quote. So there would be a match table for the interior rate, one for the exterior rate and another for the customer_id.

 

Thanks.

Edited October 21, 2012 by DeX

[requinix]

There are more in the table but this is an example. My first question is if I should have a separate table for each of these options based on square footage or if I should have them all in the same table like this. There are probably 4-5 distinct options like this.

If they're all in the same form - name, minimum, maximum, and price - then they should be in the same table. You could move the names off into another table

"id | name
---+----------------
 1 | Interior Labour
 2 | Exterior Labour

(and reference the IDs instead of the names)

 

My second question is if I should store the values into a table like so when the quote is completed:

 

quote_id | interior_labour_rate | exterior_labour_rate | customer_id

1000 | 2.45 | 2.34 | 25

1001 | 1.86 | 1.88 | 26

If you want to store the quotes, that's a business decision. I would suggest it so a customer can reference the quote they got if they have questions for you.

 

For the tables, have one table for the quote as a whole (like who, when, and how much) and another table for the values of the various variables.

"quote      | variable      | amount | unit price | subtotal
---------+---------------+--------+------------+------------
   1 |        1 |  30000 |       2.13 |    63900
   1 |        2 |  12000 |       2.34 |    28080

[Barand]

It all depends on what the others are that you haven't told us about. If you are going to introduce options such as buld material

 

timber build | 0 | 25000 | 1.90

timber build | 25001 | 50000 | 1.80

 

brick build | 0 | 25000 | 2.60

brick build | 25001 | 50000 | 2.50

 

stone build | 0 | 25000 | 2.90

stone build | 25001 | 50000 | 2.80

 

then you can't just add those to the same table as a search on the area would then pull a value for each material instead of just the one required.

 

But, as I said, we need the full picture.

 

[DeX]

Okay, here's the big picture.

 

I've got a table for building materials, everything from lumber to sheet metal to soffit to screws. Each one has a unit_price stored in the table and a quantity is calculated at quote time based on square footage.

 

When the quote is done, I store all the required building materials for that quote along with each of their individual unit prices and the calculated quantities for each material.

 

Here is an example of everything I just went over:

 

building_products:

id | name | unit_price | price_per_k | board_feet | purchase_order_id

1 | 2x4x10 | 0 | 420 | 800 | 5

2 | exterior metal | 2.33 | 0 | 0 | 3

3 | trim screws | .057 | 0 | 0 | 3

4 | ridge cap | 10.34 | 0 | 0 | 2

 

So all the products are in there. Lumber price is calculated with a formula using board_feet and price_per_k and all the others have their own unit_price. The last column is for which purchase order it gets displayed on. Different purchase orders are for lumber, metal, doors and more.

 

When the quote happens I need to store every building material used for that building:

 

match_quote_building_materials (I made up the numbers for simplicity, they would normally match their foreign keys):

id | building_product_id | quote_id | unit_price | board_feet | price_per_k | quantity

1 | 1 | 1001 | 2.12 | 0 | 0 | 12

2 | 5 | 1001 | 1.87 | 0 | 0 | 74

3 | 6 | 1001 | 0 | 800 | 420 | 34

 

And this is where I'm confused, I currently have a QUOTE table which stores the information from the actual quote itself where the user entered information. Currently it works like this:

 

QUOTE (again, numbers made up):

id | customer_id | salesman_id | date_quoted | is_insulated | drawing_price | interior_labour_rate | exterior_labour_rate | height_premium

1001 | 2001 | 3001 | 01-01-2001 | 1 | 300 | 2.34 | 3.45 | 600

1002 | 2002 | 3001 | 01-01-2001 | 0 | 300 | 1.79 | 2.21 | 400

 

And before I explain that table, there is an area in the program where the salesman can set the various labour rates based on various square footages as well as what the drawing price and height premiums currently are. They are constantly changing these values in the program (first table in first post above) based on market trends and seasonal changes. So of course I have to store the value of that option at the time of quote. Hence the drawing_price, labour_rates and height_premium columns in the quote table. There are also other things I'm storing there such as the current commission rate for the salesman, the current markup percentage on their buildings, the current commission rate for the operations manager and the list continues. They all go in this quote table.

 

My question is whether I should keep this as strictly a quote table or if I should have a separate match_quote_options table to add all these random values into or if I should have a separate match table for each value. Or feel free to tell me if I'm way off.

Edited October 22, 2012 by DeX

[Barand]

You seem to be repeating data items in several different table thereby creating data redundancy. eg

 

building_products:
id | name | unit_price | price_per_k | board_feet | purchase_order_id

match_quote_building_materials :
id | building_product_id | quote_id | unit_price | board_feet | price_per_k | quantity

 

The philosophy behind relational databases is that data is stored in one place only. You need to reconsider the design of your base data tables.

 

Also, I would have a quote table but not with the calculated totals stored as you are suggesting as they are all derived from the data in your other tables. The quote table should be something like

 

quote id | customer_id | purchase_order_no | square_footage | quote_date

 

From the square_footage in the quote you can pull the totals from the other tables when required to produce an itemised quotation.

[DeX]

Also, I would have a quote table but not with the calculated totals stored as you are suggesting as they are all derived from the data in your other tables. The quote table should be something like

 

quote id | customer_id | purchase_order_no | square_footage | quote_date

 

From the square_footage in the quote you can pull the totals from the other tables when required to produce an itemised quotation.

 

Correct me if I'm wrong but I need to store the pricing of each material at the time of the quote because the pricing changes weekly. So if the building is a confirmed sale 2 weeks later and we recalculate the pricing to send out all the purchase orders for materials, all the total pricing will be off because it'll be using the new prices.

[Christian F.]

Deleted: Was answering the spammer.

 

Now, to give some meaning to my post...

You are correct in your deduction that you need to save the prices in the quotes table, for the exact reasons you listed. It's one of the very few exceptions to the data-duplicity scenario.

Edited October 26, 2012 by Christian F.

[Barand]

The other option is to handle prices in the same way as the large enterprise models, like SAP.

 

Have a price table and for each building material hold the price and the dates it was effective

MATERIAL_ID  |  EFFECTIVE _FROM | EFFECTIVE_TO | PRICE
-------------|------------------|--------------|-------
       1    |   2012-10-01     |  2012-10-25  | 2.50
       1    |   2012-10-26     |  9999-12-31  | 2.60

 

If go with holding the calculated costs in the quotes table (easier option), create a "quote items" table with a row for each cost element, quoteID and the price.

 

 

[DeX]

The other option is to handle prices in the same way as the large enterprise models, like SAP.

 

Have a price table and for each building material hold the price and the dates it was effective

MATERIAL_ID | EFFECTIVE _FROM | EFFECTIVE_TO | PRICE
-------------|------------------|--------------|-------
1 | 2012-10-01 | 2012-10-25 | 2.50
1 | 2012-10-26 | 9999-12-31 | 2.60

 

If go with holding the calculated costs in the quotes table (easier option), create a "quote items" table with a row for each cost element, quoteID and the price.

 

Is this what I have with my match_quote_building_materials table I outlined above?

[Barand]

your match_quote_building_materials table looks more like the quote_items table that I mentioned