I am making an application that needs to sort out product based on location. A main product table is provided and can be used to refer from each location. Each location can have a different set of reference to main product table.
For eg: area_1 can have product_1 but not product_5.
I am thinking on two approaches that:
Both your ideas are bad for many reasons.
You should have one Product table with no area information in it at all.
You then create a simple cross reference table containing
Lets assume you have areas North/South/East/West
Table name = area_product
area | product_id North | 1 North | 2 North | 5 South | 1 South | 2 South | 6
And so on. You then just have to learn how to use JOIN in your query syntax.
SELECT p.* FROM product p JOIN area_product ap ON p.id = ap.product_id WHERE ap.area = 'North'
Another benefit of this structure is you can add useful info to this cross reference table for example only lets say you want to make a product available only after a specific date in an area.
area | product_id | start_selling North | 1 | 2016-01-01 North | 2 | 2016-01-01 North | 5 | 2016-07-01 South | 1 | 2016-01-01 South | 2 | 2016-07-01 South | 6 | 2016-01-01
Now to find all available products in the North area you just add a date to the criteria
SELECT p.* FROM product p JOIN area_product ap ON p.id = ap.product_id WHERE ap.area = 'North' AND ap.start_selling < NOW()