Hi All
I'm trying to come to terms with database design so I can modify an ecommerce site. Please bear with my description below.

I have 500 products on my website and 3 high st sellers to sell for. Each product has a number of fields such as model no. (primary key), weight, dimensions, text description, image name all stored in a single table. The sellers' contact details - seller_ID (primary key), name, address, email etc - are also stored in a single table.

ie 2 tables
Product_Details - details of stock
Seller_Contact_Details - details of seller

Now, each seller will have slightly different retail prices for the products they're selling and probably slightly different shipping prices and will hold subsets of the 500 products on my site and variable quantities of that product in their stock cupboard.

Currently, I have 3 additional tables to manage this - one for each seller, each looking like this:
seller id (foreign key) - product model (foreign key) - product quantity in stock - product price - shipping price.

For some reason I can't get my head around this and whether it's the optimal way to represent it. Should all 3 sellers be in separate tables or could they be in a single table? rrrrrr might go mad thinking about it. : )
Any help would be very much appreciated.
Cheers, Steve

I'm not sure I understand the end of your question? Why are there 3 tables to manage variable quantities? With FK's pointing to the product and seller, the quantity and price is the same (data type and format wise) in all cases, right?

Hi John
That's that's the mental hurdle I'm strugging with. Each of the 3 sellers are selling a subset of the total product range for different prices. eg

Seller 1 table (transposed)
ID 1 2 3 4
seller_id GU00001 GU00001 GU00001 GU00001
product_model NMCY06100 NMCY06200 NMSC03001 NMSC04001
quantity 92 89 6 85
product_price 25.52 25.49 15.99 11.99
shipping_price 2.50 2.90 2.50 2.25

Seller2 table (transposed)
ID 1 2 3 4 5 6 7
seller_id RG00001 RG00001 RG00001 RG00001 RG00001 RG00001 RG00001
product_model FFGWC05 FFGWW03 FFGWW04 FFGWW05 FFGWW06 FFGWW11 FFGWW15
quantity 88 84 44 75 20 72 17
product_price 25.52 27.99 15.99 16.45 13.20 22.80 14.60
shipping_price 2.10 2.50 2.250 2.50 2.50 2.25 1.60

I feel I should have this in a single table but I can't visualise it. I think i need to do some more reading on planning these things and dig out some examples
Steve

I would have 3 tables. Product, Seller, and ProductSellerMap, or something along those lines.

Product
----------------
ProductID primary key
Model
Description
Image
Whatever_Else_You_Need

Seller
----------------
SellerID primary key
Location
ContactName
Phone
Address
Whatever_Else_You_Need

ProductSellerMap
----------------
ProductID
SellerID
Quantity
ItemPrice
Shipping
Whatever_Else_You_Need

This will give you much better scalability, not requiring you to parse out lists within rows and columns, as shown in your extract, above. Plus, once you wrap your head around the paradigm, it will be far easier to query.