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.