October 1st, 2013, 09:31 AM
Retrieving data from two tables and make it one table?
Im stuck on how to do this.
I have two tables A and B.
A = productCode(PK):namerice
B = productCode(FK):qty:buyer
I am trying to create a new table where it becomes
That way when I list and access the products a buyer has bought, I can also access the names and price of the product.
Right now, I can only access table B and get a list of productCodes and quantity the buyer has bought but I also need to display the name of the products the buyer has bought.
If you have a better approach to what I am doing, please suggest.
Any help appreciated!
October 1st, 2013, 10:14 AM
Originally Posted by rePete
leave different data in different tables, and combine them when retrieving using a join query
do some research on data normalization
October 1st, 2013, 01:52 PM
true, but note that prices can change and you'll want to display the price of the product at the time of the purchase so it's common practise to store a copy of the price in the purchase table, like a receipt.
Originally Posted by r937
October 2nd, 2013, 03:22 AM
another way i have seen is this:
products ( product_id, name, .......)
order_items ( order_id, product_id,..)
order (order_id, user_id, date_ordered)
Product_prices ( product_id, price, start_date, end_date)
then you update this table every time price changes AND you reference this when you want to calculate the prices. This way you can get all historical prices