Help me get better at PHP - please

[DeX]

I've been on a major project for a client for the last 5 months and I have a website almost completely finished where the owner can log into an admin section and run a quote on a building for a customer. The quote gives builds a PDF quote with a price, they print it off and the client signs. It also builds all the purchase orders with the entire material list required for the building and they can click a button to email those purchase orders out to their respective building supply stores. They can also adjust the material prices, markup, add new users and such.

 

I don't have any formal PHP training, just learning as I go but I'm an extremely fast learner and know a pile of other languages. But let me tell you how I did this and you tell me if there is anything I should rewrite a proper way and avoid on any future projects:

 

- When creating a new user they have to enter the user's username and password they wish to use. The password is hashed and stored in the database (md5 I think?). The login page checks their username/password combination with what's in the database and sets a $_SESSION['xxxxx'] variable on success. At the top of every page (should be an include, I know) is a small script which checks to see if the login session variable is set and if it is, it proceeds to load the page. If not, it redirects to the login page.

 

- Database access, this is a big one, should I be writing some sort of data access layer for this? When the user inputs all the specifics of the building (width, height, length, type of post, insulation....) and clicks submit, it submits to another page that auto generates the PDF, saves it on the server and displays all of the input information in HTML format but identical to how the PDF looks. It's actually displaying the HTML that the PDF was built using (with the dompdf tool). The problem here is there is 1000 - 2000 lines of code prior to this where it has to go and pull the price of every single piece of material from the database, do calculations to find out how many of each material are required for that building and multiply it by the price per piece to get the final price. And do this for every single piece of material. Here is how I'm doing this:

$sql = @mysql_query('select price from lumber where id = '1'");
while($sqlRow = mysql_fetch_object($sql))
{
twoByFourPrice = $sqlRow->price;
}
$sql = @mysql_query('select price from lumber where id = '2'");
while($sqlRow = mysql_fetch_object($sql))
{
twoBySixPrice = $sqlRow->price;
}

.................

And that's just to get the prices for the lumber. Then I have to calculate the total pieces of 2x4 and multiply the price per piece by the total pieces and store the total price of all 2x4. It's a long process but it's done for every quote they do, hundreds per week. So by the time the quote is generated, the page has a few hundred variables stored with values. How can I make this better code? What's the proper way to do it?

 

- If they want to go back and view a quote they did last week, they go into the quotes section, scroll through them (or search) and click to open it. I'm doing the database access method above to now pull all the information that was stored for that quote in order to display it again, so I don't recalculate everything but I do still have to pull the quote price, number of windows, number of doors, width, height........and all the rest. Should I be pulling this stuff into an array or something instead of grabbing each value with its own SQL statement?

 

- This is how I have the database set up.

Lumber - id, name, price, sku, weight

Exterior Metal - id, name, price, sku, weight

Interior Metal - id, name, price, sku, weight

..............and so on with all the different types of building products

And then I store everything done in each quote:

quotes - id, customer_id, width, length, height, date, salesman_id........

And all the materials required for that quote:

quote_lumber - id, name, price, quantity, length, quote_id

quote_exterior_metal - id, name, price, quantity, length, quote_id

So when I load the quote, I just search each table for any materials where quote_id = the id of the quote I want to see. Am I doing this as simply as I can?

 

I just want to know what better methods are out there. I'm also wondering if I should be using something like Yii or CakePHP for something like this but I have only looked at those for 2 minutes each. I'm not exactly sure what it is they would do for me here. Thanks guys.

[mikosiko]

I really don't want to sound harsh... but for your own good... and for your clients... you have to consider seriously take some formal classes or read some books about database design... trust me... you need it... and after or along with that.. SQL (mysql flavor if is that what you are using)

 

The table's design that you shown in your post is terrible

- This is how I have the database set up.

Lumber - id, name, price, sku, weight

Exterior Metal - id, name, price, sku, weight

Interior Metal - id, name, price, sku, weight

 

This...

..............and so on with all the different types of building products

doesn't give you an alternate idea about how to improve your design?

 

what about replace all those tables for something simpler like this:

Table: building_products

Columns: id, name, price, sku, weight, type_id

 

Table: product_types

Columns : id, type_id, name

 

Same with you "tables" for the quote.... they are screaming for a re-design

 

Really I can't think on how are you coding all your application... and how it is working... sorry... you asked for opinion/guidance and I know what you are reading is hard... but true.

 

I understand that make changes now in your code could be a huge task and probably re-do a lot of code is not an option at this point... but for your own sake please try to learn a lot more.

[Anti-Moronic]

You sound exactly like me when I first started. I wanted to learn, but I wanted to earn also. I took on jobs that required me to expand my knowledge but it's just not good business. It's ok taking something on which will challenge you, but not something where you don't even know where to start.

 

On the above post, it's not harsh: it's the truth. But I doubt you can afford to redesign the entire application with the skills you have now. That would be the *right* thing to do, but very unrealistic.

 

Personally, if I were in your shoes, I'd use whatever I could from the fee for the project and outsource it to somebody who would either:

 

a) guide me every step of the way to finish the app and do some cleanup

b) finish it for me and comment the code as much as possible so I can learn from it

 

Stuff like this doesn't make you look good:

 

"The password is hashed and stored in the database (md5 I think?)".

 

You should know exactly, you wrote it right? You looked at the code. But then you show the enthusiasm to write a huge post so this tells me you are desperate at the moment and have bitten off more than you can chew. I've been there, trust me.

 

The only 2 options you have: hack the entire thing together and try to finish and make as stable as you can for the frontend user. OR outsource to a competent programmer and learn from this experience.

 

Search for some decent php and mysql programming books and start learning, take on smaller projects until you understand exactly what you're doing.

 

Really hope that helps!

 

 

[Anti-Moronic]

"If they want to go back and view a quote they did last week, they go into the quotes section, scroll through them (or search) and click to open it. I'm doing the database access method above to now pull all the information that was stored for that quote in order to display it again, so I don't recalculate everything but I do still have to pull the quote price, number of windows, number of doors, width, height........and all the rest. Should I be pulling this stuff into an array or something instead of grabbing each value with its own SQL statement?"

 

Yeh, you should be using select *, not select columnName, then mysql_fetch_assoc() which will fetch all columns and rows and store them into an array. You then need to learn how to effectively loop through multi-dimensional associative arrays..what I'm saying is, if this is gibberish to you, find somebody else to do these small parts for you. I know somebody who can help (not me), but it will cost you.

 

Go to elance or rentacoder and get somebody to finish up for you. Seriously, it's the right thing to do for the sake of yourself and the client.

[Psycho]

I'll echo mikosiko's statements. If you had properly set up your database, getting the data for a particular project: materials, quantity, price, etc. could be achived with one single query instead of running through a thousand lines of code.

 

To build upon what mikosiko's started, here is a rough example of how I would probably approach this:

 

Materials table: includes id, name, description, price per unit, type ID, etc.

 

Types table: includes type id and description.

(Note: if a product can belong to multiple types then you wouldn't include type id in the materials table, instead you would use a third table to make many-to-one associations)

 

Projects table: include project id, customer id, project name, date, and other "singular" data associated with the project

 

Project_detail table: this would include all the materials (and labor if needed) along with the quantity: project id, material id, qty

 

Using a structure such as this, I could get all the details of a project along with pricing with a query such as this:

SELECT p.project_name, p.project_date,
       c.client_name,
       m.material_name, t.type_name, m.material_price, pd.quantity,
       (m.material_price * pd.quantity) as sub_total

FROM projects as p
LEFT JOIN clients AS c
  ON c.client_id = p.client_id
LEFT JOIN project_detail AS pd
  ON pd.project_id = p.project_id
LEFT JOIN material AS m
  ON m.material_id = pd.material_id
LEFT JOIN types AS t
  ON t.type_id = m.type_id


WHERE p.project_id = 5

 

 

[Yeh, you should be using select *, not select columnName...

 

Um, I wholeheartedly disagree with that statement. I have seem numerous problems happen when a developer used * instead of explicitly stating the fields that they needed. Using '*' requires the database to return ALL fields in the tables being queried instead of just the data that is needed - therby conserving resources and being more efficient.

[DeX]

This is awesome, thanks guys. The criticism is welcomed, I'm not going to attack anyone for telling me the way it is. I know my database is trash but I'm here to find out how I can make it better. Or avenues of where I can start my research to make it better.

 

I should also have asked another question I've had for the last few weeks.....should I be using stored procedures or JQuery? I've heard those terms thrown around but don't mind learning it if it's useful.

 

I should also mention that I have more than enough skills to finish this, I'm actually going to incorporate all of their billing systems and everything else into it as well, so I don't need to contract it out. I just want to rewrite anything now if it helps the future of the project.

 

Materials table: includes id, name, description, price per unit, type ID, etc.

 

Types table: includes type id and description.

(Note: if a product can belong to multiple types then you wouldn't include type id in the materials table, instead you would use a third table to make many-to-one associations)

 

Projects table: include project id, customer id, project name, date, and other "singular" data associated with the project

 

Project_detail table: this would include all the materials (and labor if needed) along with the quantity: project id, material id, qty

 

Using a structure such as this, I could get all the details of a project along with pricing with a query such as this:

SELECT p.project_name, p.project_date,
       c.client_name,
       m.material_name, t.type_name, m.material_price, pd.quantity,
       (m.material_price * pd.quantity) as sub_total

FROM projects as p
LEFT JOIN clients AS c
  ON c.client_id = p.client_id
LEFT JOIN project_detail AS pd
  ON pd.project_id = p.project_id
LEFT JOIN material AS m
  ON m.material_id = pd.material_id
LEFT JOIN types AS t
  ON t.type_id = m.type_id


WHERE p.project_id = 5

 

Interesting. So you'd put all the materials into one table and load them based on the type ID? I might do that but how would I edit the pricing of each product then? What I have now is a materials management page where the owners can go in and click 'lumber' or click 'exterior metal' and view all of the pricing for each piece of material with textboxes to edit the prices. The elements are set up like so:

$all_rows = $db->fetch_all_array($sql);
foreach($all_rows as $column)
{
echo "<tr>";
echo "<td>" . $column['name'] . "</td>";
echo "<td><input type = 'text' name = 'form1[" . $column['id'] . "' value ='"."' /></td>
<input type = 'hidden' name = 'form1[" . $column['id'] . "][price]' id = '" . $column['id'] . "' value = '" . $column['price'] . "' />
............ for the rest of the attributes
echo "</tr>";
}

I just hand typed that from looking at my laptop, hopefully it's exact.

 

And the submit button on the form redirects to itself where in the PHP I have:

if (isset($_POST['saveChanges']))
{
$form = $_POST['form1'];

foreach ($form as $rowid => $columnarray)
{
foreach ($columnarray as $column => $value)
{
// create sql query here to update all values in the lumber table with all values on the page
// this works fine, just don't want to type it all out here
}
}
}

[radar]

I've been doing this for 10 years and I still occasionally put myself between a rock and a hard place.  Like this project I am currently working on, I bid it for 600, supposed to be fairly simple 10 day job.  Now I'm nearing on 30 days and I'm only about half done.

 

Interesting. So you'd put all the materials into one table and load them based on the type ID? I might do that but how would I edit the pricing of each product then? What I have now is a materials management page where the owners can go in and click 'lumber' or click 'exterior metal' and view all of the pricing for each piece of material with textboxes to edit the prices. The elements are set up like so:

 

The answer to this can vary slightly but the end effect is the same.  First step is, you'd create the navigation they click on dynamically, that way if they add a new building material, the new link displays.  In the link it would include &id=$id, which would be the id of the type of building product the link corresponds to.

 

For your display, you'd run $typeid = $_GET['id']; to retrieve it from the browser line.

 

then you'd run your query like $sql = "SELECT * FROM building_products WHERE type_id = '".$typeid."'";

 

and display off that query string.  Another option is to create a search form with a select box with all the building product types listed in it (dynamically)...

 

Then either the form action, or javascript/ajax onclick (dont forget the return false for IE compatibility) would submit the form to the proper block of code, and output the display.

[Psycho]

Interesting. So you'd put all the materials into one table and load them based on the type ID?

 

Yes, materials are "materials", thus they belon in the same table. You just differntiate between them based upon type. In fact, let's assume you need to add a new material type down the road. Using the current strcuture it would require a whole new table and probably a lot of additional code to support it. Whereas, if you had a materials table and a type table it is as simple as adding another type to that table (I assume you have admin features to add/edit materials).

 

... but how would I edit the pricing of each product then? What I have now is a materials management page where the owners can go in and click 'lumber' or click 'exterior metal' and view all of the pricing for each piece of material with textboxes to edit the prices.

 

Simple, the links on that materials management page simply pass the type ID of the materials type you want to edit. The management page takes that ID, queries the DB for all the materials that apply to that type and provides the form for edit. I don't know what you have now, but I suspect you have independant pages for editing all the different material types. With this logic you just need one.

[jcbones]

I suggest a series of videos on data logic modeling, and normalization.

 

[DeX]

The answer to this can vary slightly but the end effect is the same.  First step is, you'd create the navigation they click on dynamically, that way if they add a new building material, the new link displays.  In the link it would include &id=$id, which would be the id of the type of building product the link corresponds to.

 

For your display, you'd run $typeid = $_GET['id']; to retrieve it from the browser line.

 

then you'd run your query like $sql = "SELECT * FROM building_products WHERE type_id = '".$typeid."'";

 

and display off that query string.  Another option is to create a search form with a select box with all the building product types listed in it (dynamically)...

 

Then either the form action, or javascript/ajax onclick (dont forget the return false for IE compatibility) would submit the form to the proper block of code, and output the display.

Interesting, maybe I can simplify all the different material management pages into one single page and have a submit button for each section of materials. Or would you have one single submit button at the bottom to save all the materials on the page and their prices/sku/weight accordingly? By the way, what would be the benefit to having a single table instead of all separated like I have it now? And also, would it matter that some materials have a material price as well as a labour price and others just have a material price? Then others have lengths, some don't, and others have attributes like a window can be sliding or fixed. I'd have a lot of NULL cells for materials that don't have those attributes.

 

Yes, materials are "materials", thus they belon in the same table. You just differntiate between them based upon type. In fact, let's assume you need to add a new material type down the road. Using the current strcuture it would require a whole new table and probably a lot of additional code to support it. Whereas, if you had a materials table and a type table it is as simple as adding another type to that table (I assume you have admin features to add/edit materials).

I think I'm the only one right now that can add new materials because of the way the building price is calculated. When the quote is done, there are algorithms run on each material to figure out how many of each are needed, like for each walk-in door in the quote, it would add 3 pieces of 2x4 to build the frame of that door, get the price from the lumber table and multiply it by the quantity to get the total price of 2x4, then add that to the total price of the building. And right now I've got to add caulking so we figured it out that they use 2 tubes of caulking per door and also 1 tube for every 10 feet of length on the eve. So I need to write the code to get all those variables and do the math, then add it to the building price. Unless you have a way they can add a new material (like tubes of caulking) and enter the calculations for it themselves? That would be amazing but I haven't figured out a way yet.

 

Simple, the links on that materials management page simply pass the type ID of the materials type you want to edit. The management page takes that ID, queries the DB for all the materials that apply to that type and provides the form for edit. I don't know what you have now, but I suspect you have independant pages for editing all the different material types. With this logic you just need one.

So like I said up top here, just one single page to edit the material attributes? And have them all listed in a long table or divs with their price, length, sku, weight....?

 

I suggest a series of videos on data logic modeling, and normalization.

 

Is that the terms for what I'm trying to do here? If that's what I need to do then thanks for getting me started.

[Psycho]

think I'm the only one right now that can add new materials because of the way the building price is calculated. When the quote is done, there are algorithms run on each material to figure out how many of each are needed, like for each walk-in door in the quote, it would add 3 pieces of 2x4 to build the frame of that door, get the price from the lumber table and multiply it by the quantity to get the total price of 2x4, then add that to the total price of the building. And right now I've got to add caulking so we figured it out that they use 2 tubes of caulking per door and also 1 tube for every 10 feet of length on the eve. So I need to write the code to get all those variables and do the math, then add it to the building price. Unless you have a way they can add a new material (like tubes of caulking) and enter the calculations for it themselves? That would be amazing but I haven't figured out a way yet.

That sounds fairly simple to me. Not that it won't take time, but the logic should be simple. In addition to raw materials you also need to define specific "products" that are made up of various quantities of raw materials. I see no reason to put this logic in the code. Just create the necessary db tables to define the products and the materials and quantities needed of each. All of this can be done so anyone can create new "products". I would think that an estimate would require the estimator to enter in individual products with the option to enter additional quantities of raw material as needed.

 

So like I said up top here, just one single page to edit the material attributes? And have them all listed in a long table or divs with their price, length, sku, weight....?

 

It is one "script", but that script would be used to generate multiple pages - if that is what you want. You apparently have multiple pages for each material type now. There's no reason you can't keep doing that but with one single script that uses the material ID to build the page to be specific for the materials of that type. Build it to look however you wish.

[jcbones]

Is that the terms for what I'm trying to do here? If that's what I need to do then thanks for getting me started.

 

Yes, those videos will teach you how to build a database optimally.

[DeX]

I've been watching some of the videos, looking at database normalization, researching MVC and looking at J2EE/struts, thanks you guys helped a lot. Now I have another question. It would be easy enough to add a certain number of 2x4 for every specific option chosen to be added to the building but the calculations we're using are more complicated. Without hard coding the calculation, how can I allow new materials to be added to a building? For example, I just added the cost of caulking to the building prices so that's calculated at a half tube for every door and full tube for every 50 feet of building, rounded up to the nearest tube. Or extra labour that's added at the cost of (squareFootage / postSpacing - 1) * 0.65?

 

The way it is now I had to go in and add the caulking to the database, then add the calculation into the cost of the building, how could I make all materials dynamic so the admin can change the calculations on the fly? Without knowing how to code.

[Psycho]

The way it is now I had to go in and add the caulking to the database, then add the calculation into the cost of the building, how could I make all materials dynamic so the admin can change the calculations on the fly? Without knowing how to code.

 

I don't know if you are familiar with the construction estimation process or if you are just winging this as you go. Whatever the case you need to make sure you fully understand the process before you start coding this stuff. I've worked with estimation processes in a couple of industries (one where I was very familiar and another where I wasn't). You need to walk through every single aspect of the estimation process to determine what the variables are. E.g. how is each material estimated for the project. THEN, analyze the processes to build an appropriate model.

 

When that analysis is done you might find that there are different variables for materials based upon different factors. For example, I would expect that material such as copper pipes, drains, solder, etc. are only dependant upon the number plumbing fitures (sinks, bathtubs, toilets, etc). Or maybe they prefer to estimate based upon the number of full/half baths. So, for those "class" of supplies you would provide the appropriate parameters.

 

For the latter example above you could provide fields for Qty per kitchen, qty per full bath, qty per 1/2 bath. Or if it will be based upon the number of plubming fixtures it may be adequate to have a single value for qty per fixture. But, if the qty will vary based upon ficure then you would need to be able to provide fields for each fixture type (qty per sink, qty per toilette, qty per tub, etc.)

 

With caulking, drywall, joint compound, baseboards, etc, you probably need a different class of materials. These would be estimated based upon squarefootage, height of walls, # of doors, etc.

 

If you don't want to go to that level of detail you could try the kitchen sink approach (pun intended). Create a form for each product that includes every possible parameter:

Qty per rooms

Qty per doors  [.5]

Qty per sqr foot  [.02] (1 tube/50ft)

Qty per sinks

Qty per tubs

Qty per ...

 

Then let the user determine which parameters are appropriate for the material. As for labor, again, it depends on what the model is. However, based upon the formula you have above "(squareFootage / postSpacing - 1) * 0.65?" I think it would be very difficult to create an intuitive form if the calculations will be that variable. I would think that a labor amoutn per unit of material would be workable. But without completely evaluating the requirements from the user it is impossible for me to suggest a best solution. It really would take a deep analysis of the current estimation process.

[DeX]

That's some good ideas, that actually helps a lot, thanks.

 

So I spent the evening redesigning the database and putting some normalization into it. Here's what I have now:

 

table_products

------------------

id

product_id

type_id

purchase_order_type_id

colour_type_id

name

material_price_per_piece

labour_price_per_piece

price_per_k

board_feet

sku

weight

 

 

product_types

------------------

id

type_id

name

 

 

colours

------------------

id

name

 

 

colour_types

-----------------

id

type_id

name

 

match_quote_colour

-------------------

id

quote_id

colour_type_id

colour_id

 

 

purchase_orders

------------------

id

type_id

name

 

 

quote

-------------------

id

customer_id

building_type_id

salesman_id

price

width

length

height

gable_post_spacing

eve_post_spacing

pitch

heel_height

double_ply_rafters

perma_columns

insulated

markup

ridge

date

 

 

match_building_product

---------------------

id

quote_id

building_product_id

building_product_quantity

building_product_length

 

 

building_type

---------------------

id

name

 

 

customer

---------------------

id

first_name

last_name

address_1

address_2

phone

fax

email

city

province

postal_code

 

 

user (salesman)

--------------------

id

first_name

last_name

username

password (hash)

phone

email

access_level

commission_rate

 

 

Does that look a lot better? Am I using the match tables correctly?

To go through this quickly, table_products and product_types match up if the product is lumber or metal or whatever else. The colours are to put on the purchase order, the client chooses a door colour, roof colour, wall colour and all the rest. So any pieces that are trim will be associated with colour_type of trim and therefore will show up on the purchase order with the trim colour next to them. The quote has all the quoted information for the building. The match_building_product table matches the quote with which building products will be used for it and the quantity of each as well as length. Table building_type is used to store which kinds of buildings they offer (hayshed, riding arena...) and then it's displayed on the quote so the associated ID is stored in the quote table. Customer holds the customer's details and user holds the salesman's details and access level.

 

 

I have a few questions with the left over fields that I couldn't put in anywhere.

 

1. Quotes are given out by the bucket full but only some are confirmed to be made into actual buildings and there's a button on the quote page to confirm it. When that gets confirmed should I have a record in the quote table get changed from 0 to 1 or should I have a confirmed table that has quote_id listed in it for confirmed quotes?

 

2. Should I be storing absolutely everything in the quote table? For example there is a certain labour rate that is given to buildings between certain ranges of square footage. I can derive the labour rate for any given quote by looking at the square footage and getting it from some labour_rate table but what if the labour_rates were changed since the quote was done? Should I be storing what the labour rate was at the time of quote? That would mean I would have to store every variable used in the calculation of the building including the markup percentage, profit margin, any buffers, salesman commission rate, everything.

 

3. There's a place on the quote page to add a custom charge for anything the salesman wants to charge for like if they have to rent a Bobcat for a week and add that to the quote price, something one off. This is not going to be in the building_product table so where would I store the cost and discription for it? In the quote table? In its own table?

 

4. There are 4 different kinds of posts which can be used in a building, 3ply, 4ply, 3plyconcrete, 4plyconcrete. Would I add these as 4 separate rows in the building_products tables or would I add a separate posts table and use a match table?

 

5. Purchase orders are huge, this application is going to auto create the purchase orders and fire out a PDF with a button click. To decide which products go to which purchase order I think I would add another column to the building_products table titled purchase_order_id and then have a purchase order table that lists all the different purchase orders they want to create, matching each product with the correct purchase order it falls onto. Then how would I specify the column names for the purchase order? Hard code them? Put them into a table? Add them as another column to the building_products table?

[Psycho]

Does that look a lot better? Am I using the match tables correctly?

It "looks" ok, but I can't tell you if it is what I would do or if there are any specific problems because I don not understand all the relationships of your data and the specific needs of the user. To do that would take a LOT of time. The only thing that doesn't make sense to me is in the table_products you have two fields "id" and "product_id" - those look like they would be for the same purpose.

 

1. Quotes are given out by the bucket full but only some are confirmed to be made into actual buildings and there's a button on the quote page to confirm it. When that gets confirmed should I have a record in the quote table get changed from 0 to 1 or should I have a confirmed table that has quote_id listed in it for confirmed quotes?

Again, this comes down to analyzing the specific requirements for the application. Typically, in a situation with estimates which may become jobs I would have a table for the estimate records (which of course are tied to various other tables for materials, labor, etc) and then I would have a separate table for the job tickets. The job tickets table would have a reference back to the estimate record (or records if appropriate for the application). The job ticket could also be used to track actual materials/labor which is helpful during the billing process and to validate the accuracy of the estimating logic.

 

2. Should I be storing absolutely everything in the quote table? For example there is a certain labour rate that is given to buildings between certain ranges of square footage. I can derive the labour rate for any given quote by looking at the square footage and getting it from some labour_rate table but what if the labour_rates were changed since the quote was done? Should I be storing what the labour rate was at the time of quote? That would mean I would have to store every variable used in the calculation of the building including the markup percentage, profit margin, any buffers, salesman commission rate, everything.

Again, depends on the needs of the user. But, I would "assume" that you want to store the calculated values (not the formulas) with the estimate. That way you will know what was presented to the customer. Depending on the needs you could add a "recaculate" feature to recalculate the estimate based upon any changes to the formulas. But, if so, I would again assume that would create a new estimate so you don't lose the history of previous estimates.

 

3. There's a place on the quote page to add a custom charge for anything the salesman wants to charge for like if they have to rent a Bobcat for a week and add that to the quote price, something one off. This is not going to be in the building_product table so where would I store the cost and discription for it? In the quote table? In its own table?

I would put that in a separate table for "additionals". You would store a separate record for each "additional" charge and link each back to the respective estimate.

 

4. There are 4 different kinds of posts which can be used in a building, 3ply, 4ply, 3plyconcrete, 4plyconcrete. Would I add these as 4 separate rows in the building_products tables or would I add a separate posts table and use a match table?

I would think that they are logically 4 different products. But, if the user wants to be able to select "posts" and then have a sub-selection for the post type, then you would need to figure out how best to achive that. The easiest method is to simply treat them as separate products.

 

5. Purchase orders are huge, this application is going to auto create the purchase orders and fire out a PDF with a button click. To decide which products go to which purchase order I think I would add another column to the building_products table titled purchase_order_id and then have a purchase order table that lists all the different purchase orders they want to create, matching each product with the correct purchase order it falls onto. Then how would I specify the column names for the purchase order? Hard code them? Put them into a table? Add them as another column to the building_products table?

Not sure here. Again, making some assumptions, I would think that the POs are specific to certain vendors and that specific products will always be purchased from specific vendors. So, the products would need a reference to the vendor that the product will be purchased from (in a vendor table). For the purchase orders I would have at least two tables. The main PO table would include an ID back to the vendor along with non-duplicative data about the PO (e.g. date). For the line item details of the PO (i.e. the materials) I would use a second table which would have (at a minimum) a reference to the PO ID, the material ID, the material QTY, the material unit cost. You would need to store the material unit cost in this table even though it should already be in the material table because if the material cost changes you still need to know what the cost was on previous POs.

[KevinM1]

I just wanted to say that this thread is great as a "My first professional web app" case study.  It should be stickied and pointed to whenever someone asks about what to consider in terms of db construction, project management, and the like.

 

Also:

 

should I be using stored procedures or JQuery? I've heard those terms thrown around but don't mind learning it if it's useful.

 

jQuery is a JavaScript framework that has absolutely nothing to do with database queries, or the database at all, for that matter.  It's a minor thing, but I didn't see anyone correct it.

[DeX]

I've come to another problem. In the very first form for filling out building options there are a series of doors where you can choose a width, height, quantity and some other miscellaneous options for each. Then there's another type of door which only has a quantity to select and then some windows which only have quantities.

 

Would you store these in the same product table even though they have their own specific set of attributes?

 

When someone is looking at a quote they have the ability to edit it and land back on the same form page with all the fields filled out with the database values from that quote. It would have to display all the correct widths/lengths/heights and other options in the <select> boxes supplied. And each option has the ability to click a + button at the end of a row to show a new row which can then be filled out as well. All extra rows must also be populated when editing the quote.

 

I already have all this working right now, I'm just wondering how I would normalize the database and make things more efficient. Right now I have tables to store each type of option with all their own attributes as columns in that table, then store the quote ID with it.

 

I'm stuck on this one, thanks.

[Psycho]

As stated before, I cannot really offer any suggestions that I think are appropriate for your situation wiothout doing the analysis work. And, to be honest, I think I have invested way more time into this post than I should.

 

To answer your question simply, I would put all materials into a single materials table. If som material requires additional parameters, I would proabably put those parameters into a separate table (material_parameters?). That table would list the parameters along with the appropriate values. Plus, each parameter may have different types of inputs (select list, radio group, etc.). So, in effect, you would need to build dynamic form logic. Plus, when saving a quote you would want to store the selected parameters in a separate table as well.