匹配一个列值与mysql中的多个值

Question

Ok, so I am creating a sort of online shopping service and I have a DB that requires a field to match up against multiple other fields. They need to be stored, so that they can be ignored and set as maybe for later viewing. I know that setting up a comma delimited field is usually unwise, but I cannot figure out a better way to keep track of the matches while being able to categorize them into separate columns later on.

For eg, I have 25 products to sell and have 10 cities in which I want to sell them. Some can be shipped to 5 cites and some can be shipped to 9 cities. Now if someone searches for products in one particular city, how will only those products appear as a search result that are available for sale in that city only?

Does anyone have any better way of doing this without the comma delimited field?

Answer 1

use a n:n Table:

Products Table

products

id | name

city table

cities

id | name

Mapping (unique constraint over BOTH columns, not single constraints):

shipping_information

product_id | city_id

Then you can easily select all available cities for a product, or select all products, which are shipped to a certain city.

All available cities for product 4: SELECT * FROM cities, shipping_info WHERE cities.id = shipping_info.city_id AND shipping_info.product_id = 4

All available products for city 3: SELECT * FROM products,shipping_info WHERE shipping_info.id = 3 AND shipping_info.product_id = products.id