MySQL：基于国家的产品列表

Question

I have a table of products. I want to show the visitor products based on their country (IP location). The table looks like this:

Product ID, Name, Price, Size, Color, ...

This table is "read-heavy", I seldom write new entries to it.

I thought of this solution: Add to the table every country as a column. There I can set true or false depending if I want to show a certain product to visitors from a certain country.

Product ID, Name, Price, Size, Color, ...,  USA, France, Spain, China

There are about 200 countries so the table will have many columns. The script gets an incredible amount of request. The query will be called +1million per minute. So I need some solution with good performance. But I don't think this solution is good. The most important factor is performance.

I am using PHP, MySQL.

Answer 1

That will be a nightmare to maintain. As countries come and go you will have to modify your model accordingly. I would suggest something like:

CREATE TABLE PRODUCT_COUNTRY -- perhaps there''s a better name for this
( PRODUCT_ID ... 
, COUNTRY_CODE CHAR(3) NOT NULL
,    CONSTRAINT PK_PRODUCT_COUNTRY PRIMARY KEY (PRODUCT_ID, COUNTRY_CODE)
,    CONSTRAINT FK_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS (PRODUCT_ID)
,    CONSTRAINT FK_COUNTRY FOREIGN KEY (COUNTRY_CODE) REFERENCES COUNTRIES (COUNTRY_CODE)
) ENGINE = INNODB