Design Company -> Location -> Product Database

Question

I'm trying to design tables : Company -> Location -> Products tables for a project I'm working on

So basically a company has multiple locations.

Each location can have the same of different products and also products can have different prices across different locations.

So my question is, how can I efficiently design this use case because my problem is that:

• If I put the PRODUCTS under the COMPANY Level --> (Product cannot have different prices etc..) but it is easy to REPORT on when doing analytics.

• If I put the PRODUCTS under the LOCATION Level --> It gives me the possibility to give different prices to the same product but TECHNICALLY they are TWO different products on the database and cannot report on them easily.

Is it good to add a PRODUCT table and add another table between location and product (PRODUCT_LOCATION) that will have the price and other properties specific to a location?

Thank you for taking the time to read my question,

Answer 1

You can use a mapping table for Location and Product and then using primary key for this, you can create a mapping table with LocationProductId and Price.