2

I have database with about 50k (and counting) products. Further, users on my website have declared the country of their origin.

Now I need to add to products fields like allowed_countries and blocked_countries.

  • Product can have these fields empty - it will be shown to all users,
  • Product can have allowed_countries filled - it will be shown onlyto users from that countries,
  • Product can have blocked_countries filled - it will be shown to all users, except users from blocked_countries list.

Such filtering should apply in product catalog, product page and searcher (currently full text search).

How to efficiently design a database for such requirements? What I am caring is performance under quite big load. I was thinking about dynamic columns in MariaDB 10.0.20, but I am not sure about speed of filtering by exactly one country.

user1292810
  • 1,702
  • 7
  • 19
  • 38

1 Answers1

2

Use a linking table

products table (id, other data)

countries table (id, name, etc)

linking table (product_id, country_id, allowed) all not null, 1 means allowed 0 means diallowed

select * from product 
left join linking linking_table on linking_table.product_id = product.id 
left join counties_table on (countries_table.id = linking_table.country_id)

Now the where changes like this

allowed counties 

where countries_table.id = (USERS COUNTRY) and linking_table.allowed = 1

not allowed

where countries_table.id = (USERS COUNTRY) and (linking_table.allowed = 1 or linking_table.allowed is null)

The second adds the case where the product doesnt have any black list.

This could return multiple rows per product, so you will need to either group by or return distinct

exussum
  • 18,275
  • 8
  • 32
  • 65