Page 1 of 1

Add products by retailer

Posted: Fri Jun 21, 2013 2:33 am
by hance
hello guys, i have a login table where customer and retailer logins are stored. roles that are defined in the table are retailer and customer next to each login details.

customers and retailers both are given user_ids when registering. since i have many retailers who will add the same products but with different product details and prices, i want to know how to differentiate between those retailers when they add products. it will be much easier to do a query by retailer then.

every product added are stored in the tbl_product table in the database. queries are done from that table.

suppose retailer A has added product A, retailer B has added product A as well but with a different description and a different price.

is it possible? can i have a tutorial somewhere?

Re: Add products by retailer

Posted: Fri Jun 21, 2013 3:55 pm
by mecha_godzilla
Hi,

One way to do this would be to have two tables - one to store the main product details (called something like "tbl_product_description") and one to store each retailer's "instance" of those products. So "tbl_product_description" stores a basic level of information about the product (product name, product category, bar code, etc.) generic to all retailers, and "tbl_product" then stores a unique entry that records the retailer's price, quantity, etc.

The "tbl_product" table may then look something like this:

id: 1 (PK, auto-incremented)
productDescID: 2200 (FK, associated with each product's ID in "tbl_product_description")
retailerID: 55 (FK, associated with each retailer's ID in "tbl_retailers")
price: 500.00
quantity: 2

If you want each retailer to create their own description for each product, you just need to record this information in "tbl_product" instead of "tbl_product_description".

When you want to find out which products a retailer is currently selling, you then just need to create a query similar to this:

Code: Select all

SELECT tbl_product.id, tbl_product.price, tbl_product.quantity, tbl_product_description.productDescName, tbl_retailers.retailerName
FROM tbl_product
JOIN tbl_product_description ON tbl_product.productDescID = tbl_product_description.id
JOIN tbl_retailers ON tbl_product.retailerID = tbl_retailers.id
WHERE tbl_retailers.id = '$retailer_id'
Hopefully that makes sense :mrgreen: The table arrangement would in theory allow retailers to create more than one entry for the same product, so you need to manage this in your script or queries.

Mecha Godzilla