mysql - 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,

标签： mysqlsqldatabasedatabase-designmodel

Answer 1

您可以为 Location 和 Product 使用映射表，然后为此使用主键，您可以使用 LocationProductId 和 Price 创建一个映射表。