Hi I am fairly new to php mysql. I am trying to figure out how I could go about storing this data in a table. Here is my example. I am setting up an estimate type table I got all the basics covered customer name, Description, date, type, etc. I then have several options I would like to be able to attach. Lets say I setup an estimate then I would like to attach Item1 with a quantity of 3 and item 8 with a quantity of 20. Now how would I go about attaching each item to the estimate, keeping in mind there could be an unlimited amount of options attached. Should I make a seperate table where I store the estimateID, ItemID, quantity, etc. I am sure there is a term or something I am referring to maybe database relationship, idk. Like I said I am fairly new to everything and primarily learned everything specifically for this project. A point in the right direction would be great. I typically solve my problems with a dozen or so searched on google however I am not sure what to search on this. ~~~~~~~EDIT~~~~~ I realize I probablly didn't leave enough details. Each estimate I setup the Customer which I have a table for. Each estimate you can select a pricing level say Low=.05% Markup, Medium=.10% Markup, etc.. I understand that typically I would have Estimates_table with the following fields. estimateID, CustomerID, itemID, ItemQuantity, However lets say I Update the prices after the estimate is created. The prices in the estimate would change also. I guess I can correct this problem by adding a PriceColumn. What about the Markup should I also create a markup column for storing that.
yes you should create extra tables and it's called normalization of databases. In the past scripters/prorammers put all information into one or 2 tables, these days its better to use more tables.
In my opinion, you should have 1 database table for the basic things, and 1 (or more) database tables for the unlimited extra data. The two tables are NOT connected. You connect them by making a query, for example INNER JOIN estimates ON estimates.id = customer.id. It is saying that you're connecting the value stored in "estimates" (all the extra data) to the basic data, based on the id. For example, you got a customer with id 12345, and you got a lot of data in the estimates table. With a query, you attach the ones which are related to customer id 12345. If you want a changing database, you need to make a query to change the database. Either when you order it, or as a cron job which would run periodically. Is this making any sense?
yes it makes sense. I wasn't real sure about how joining the tables actually works. I couldn't find much info on it. I will try to dig a little deeper. In the mean time I just put a few extra columns in each table. So that I can attach say a specific thing to -> estimateID. Than in the estimate table I just have a column for the customerID. It get the job done for now. I still want to look into join table and foreign keys though. Thanks for the help every bit helps.
There are two things you'll need when joining the tables with a query. There is INNER JOIN and LEFT JOIN. There is also RIGHT JOIN but I haven't found much use for it. As a rough guideline, I'm using LEFT JOIN when INNER JOIN doesn't produce the wanted results. LEFT JOIN is a bit faster but it produces a larger output as it just attaches the values to the left, instead of joining them in the same row. Here is an example. I got two tables. One (sport) contains information about a sport (football, basketball, baseball...), and another one (stage) contains information about the tournament stage (Bundesliga, MLB, NBA...) and it has a sportFK column. For example, baseball is 34, and sportFK for MLB is 34 - so MLB is obviously a baseball league. Here is how to connect the two tables to get an output which says "34 Baseball MLB, 34 Baseball Japan, 35 Basketball NBA..." In all such queries it's important to select FROM table which has more entries. In this case, that's "stage". SELECT sport.id AS sportid, sport.name AS sportname, stage.name AS stagename FROM stage INNER JOIN sport ON sport.id=stage.sportFK ORDER BY sportid ASC, stagename ASC http://www.w3schools.com/sql/sql_join_inner.asp