How to define a column in a database with multiple values ​​specified in another table?

Question

I am working on a website, some of the features are similar to Stackoverflow.

One of them is a few elements of the type (in SO they assume several questions).

Each element is associated with several values ​​of a different type (City). (Assume that each quest is associated with several tags. A tag is an entry from the say Tags table).

Hence my tables:

Cities(Id, Name, IsAcive) MyItems(Id, DisplayText, AciveInCities) 

Here, one myitems entry can be active in several cities.

My question is how to define the AciveInCities column in the database. Comma-separated CityIds or comma-separated city names or some where in different tables?

This is a web application in which I would like the user to be able to search MyItems by city. Therefore, storing cityname / id in one table can be quick.

Are there any issues with any of these three approaches?

If this is a duplicate question, please redirect me to fix one, I could not find it.

thanks

Answer 1

You should not have multivalued columns in your database - this is not normalized .

Contains a many-to-many table with CityId and ItemId as foreign keys and which together represent a composite primary key.

 Cities(Id, Name, IsAcive) MyItems(Id, DisplayText) ItemsActiveInCities(CityId, ItemId) 

Answer 2

The third associative table will be the normalized approach:

 City (CityId, Name, IsAcive) Item (ItemId, DisplayText) ItemActiveInCity (ItemId, CityId) 

---

As for why storing comma-separated values ​​in a database column is not a good idea, see this: Is storing a comma-separated list in a database column really that bad?

Answer 3

All the words would not be recommended , since the intersection of the / col line should be one cell , so if you created another column that represents this , for example , if my project is a library, and my task is to list the names of customers who bought "Maths for elementary school students" and "Sahih Muslim explanation" in SINGLE order.

This means that I need a book list (IDS) in one order.

solution :

Define a column such as orderID (primary key), but allowing you to repeat, which just allows you to combine these rows later.