In a Postgresql database where a seller has many products and products have many sellers, where should price(s) get stored?

user_5096832 0
Viewed 986

Using django and python, I am building a web app that tracks prices. The user is a manufacturer and will want reports. Each of their products has a recommended price. Each product could have more than one seller, and each seller could have more than one product. My question is, where do I store the prices, especially the seller's price? Right now I have my database schema so the product table stores the recommended price and the seller's price, and that means one single product is repeated a lot of times. Is there a better way to do this?

DB Schema

Per the recommendations below this is the correct db schema: enter image description here

postgresql schema database
3 Answers

Since you have a case of many-to-many then your structure would use a link table. You’ll have tables seller, product and link_seller_product. The last table has a link to the seller table via id as well as the product table via id. This table therefore can also have any information that is dependent on the seller and the product and is not fixed for either. So price-per-product-per-seller goes there.

So add the additional link table with columns sellerid, productid and price and you’ll have only single rows in sellers and products but each seller can have their own price for the product.

user_18067800

You're not adequately representing the one-to-many relationship between products and sellers. Your product table has the seller_id and the seller_price, but if one product is sold by many sellers, it cannot.

Instead of duplicating product entries so the same product can have multiple sellers, what you need is a table between products and sellers.

CREATE TABLE seller_products (
   seller_id integer,
   product_id integer,
   price decimal 
);

I'll leave the indexes foreign keys etc to you. Seller ID and product ID might be a unique combination ( historical data is best removed from active datasets for performance longevity ) , but of course any given product will be listed once for each seller that sells it and any given seller will be listed once per product it sells ( along with its unique price).

Then you can join the table back to products to get the data you currently store denormalized in the products table directly :

SELECT * 
FROM products
LEFT JOIN seller_products ON ( seller_products.product_id = products.id)
user_17260830

This is a Data Warehouse question.

I would recommend putting prices on a Fact as measures and having only attributes on the Dimensions.

Dimensions:

  • Product
  • Seller
  • Manufacturer

Fact (Columns):

  • List item
  • Seller Price
  • List item
  • MRSP
  • Product ID
  • Seller ID
  • Manufacturer ID
  • Timestamp
user_91567990
Related