user3335496 user3335496 - 4 months ago 6
MySQL Question

Use multiple tables with identical names or single table with large no. of entries

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:

  1. Have tables table_area(areaId), table_product(productId) and table_area_product(areaId, productId) and sort products based on areaId provided.

  2. Have multiple tables such as table_area_product_{areaId}, eg: area_1(table_area_product_1).

How to write a mysql function with dynamic table name?
, this link helps on usage of dynamic table name but also says not to use it but I couldn't find any further explanation.

Product data can be 1 lakh entries and area data can be upto 300 entries. Both will most probably increase over time.

What should be the best approach? Or may be there is a third one too?


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 area and product_id

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.

FROM product p
    JOIN area_product ap ON = 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

FROM product p
    JOIN area_product ap ON = ap.product_id
WHERE ap.area = 'North'
  AND ap.start_selling < NOW()