As per now i have 3 tables. a customer table, a product table and a cart table. customer table have cid as primary key. product table have pid as primary key.
now i want to add products to cart table. also i want to know which customer is adding that product.
if i am taking cid and pid into cart table,while the customer orders more than one product then redundancy occurs.
how can i create a table relating all those information? that is who is ordering, which product and the quantity.
MyTables
Product_Table( Prod_id, Grade, Estate, State, Pkgs, Wpkg, Cost, Desc, );
Customer_Table( Cust_id, Cname, Company, Address, Phone, Email, Country, City, );
Cart_Table( Cart_id, Prod_id, Cust_id, Qty, Totalcost, );
To avoid the redundancies, you should add another table Cart_Positions that contains all products ordered, while cart only contains the Customer ID.
Cart_Table( Cart_id, Cust_id, ... );
Cart_Positions_Table( Cart_Positons_id, Cart_id, Prod_id, Qty, ...);
(Primary keys in bold, foreign keys italic)
EDIT: When trying to avoid redundancies you should also think about the "TotalCost" you already have - this could easily be calculated by using the product prices and the quantity. On the other hand, if you allow discounts but don't store it in the order (which is not a good idea) the total cost column would be necessary.