SQL field with multiple identifiers of another table

Question

Can someone give me an idea on how to create this database structure. Here is an example:

Table "countries": id, countryname 1, "US" 2, "DE" 3, "FR" 4, "IT" 

Now I have another table "products", and there I would like to store all the countries in which this product is available:

 Table "products": id,productname,countries 1,"product1",(1,2,4) // available in countries US, DE, IT. 2,"product2",(2,3,4) // available in countries DE, FR, IT. 

My question is: How to create a table structure in "products" in order to be able to store several countries?

My best idea is to place a comma-separated string there (i.e., "1,2,4"), and then split that string to search for each record. But I doubt this is the best way to do this?

EDIT: Thanks everyone for your help, awesome! It was hard to choose the right answer. I finally chose Greg because he pointed me to the JOIN explanation and gave me an example of how to use it.

Answer 1

You need an intersection table for many-to-many .

 Table Country CountryID, CountryName Table CountryProduct CountryID, ProductID Table Product ProductID, ProductName 

You then Inner Join all 3 tables to get a list of countries and products.

 Select * From Country Inner Join CountryProduct On Country.CountryID = CountryProduct.CountryID Inner Join Product On CountryProduct.ProductID = Product.ProductID 

Answer 2

Without denormalization, you will need to add an extra table

 Table Product countries ProductID CountryID 1 1 1 2 1 4... 

Answer 3

What you are talking about is normalization. You have a many-to-many structure, so you must create another table to link the two. You should never (usually, almost never) use delimited strings to store a list of values ​​in a relational database.

Here is an example setup:

 product_countries table productid | countryid ----------+----------- 1 | 1 1 | 2 1 | 4 2 | 2 2 | 3 2 | 4 

You can use a foreign key for every other table, and then make both of them into a composite primary key.

Then you can get a list of supported products for the country identifier, for example:

 SELECT * FROM products, product_countries WHERE products.id = product_countries.productid AND product_countries.countryid = $cid 

Answer 4

You can also create a third countries_products table with the country_id and product_id fields.

Answer 5

The best approach for relational databases is as follows:

One couture table, say, country_id, country_desc (country_id is the main)

one table for products, say product_id, product_desc and as many columns as you want (product_id is the main)

If you had only one country, it would be enough to have a foreign key pointing to country_id in each line of the product. The presence of a foreign key states that there is a real country representing the country country_id belonging to the country table.

In your case, you have several countries for the product, so add a separate association table product_id, country_id

both main keys and both foreign.