Let's say I have n locations and p amount of products. Each location would have some, but not necessarily all of those products and their own, location specific product metadata (e.g. quantity). What is the most efficient way of establishing an ownership relationship between each location and each individual product on the list?

I was considering an 'ownership' table with columns like a primary key, a foreign key for the location, a foreign key for the product, and subsequent columns for location specific meta data. Obviously, with this solution I'm looking at n * p values in that 'ownership table'. Is there a more efficient way of doing that? Maybe something more like n p?

This has to be done in MySQL.

CodePudding user response：

Looks like I'm working with a many-to-many relationship. Best solution I can find has related table populated with primary keys from the two, shared datasets. I.e. columns include: a primary key for the table, two foreign keys corresponding to the primary keys of the two datasets, and additional columns for relation-specific metadata.

CodePudding user response：

Sounds like 3 tables:

• the main table has 3 columns: location_id, product_id, quantity. PRIMARY KEY(location_id, product_id), INDEX(product_id)
• A table locations has columns for location_id, address, etc. If price is common across locations, then that should be a column. PRIMARY KEY(location_id)
• A table products has columns for product_id, name, description, and other common attributes, plus a JSON column for the random infrequent attributes. PRIMARY KEY(product_id)