How to define column in database having multiple values referenced in another table?

Question

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

One of them is it has multiple items of a type(in SO assume, multiple question).

Each item is related to multiple values of another type(City).(In SO assume, each queston is related to multiple tags. a tag is entry from a table say Tags).

Hence my tables are:

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

Here one myitems entry can be active in multiple cities.

My question is how should I define AciveInCities column in database. A comma separated CityIds or comma separated city names or some where in different table?

This is a web application where I would like to user be able to search MyItems by cities. Hence saving cityname/id in same table could be fast.

Are there any problems with any of these three approaches?

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

Thanks

Answer 1

You shouldn't have multi-valued columns in your database - it is not normalized.

Have a many-to-many table with CityId and ItemId columns as foreign keys and which together are a composite primary key.

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

Answer 2

A third associative table would be the normalized approach:

City
(CityId, Name, IsAcive)

Item
(ItemId, DisplayText)

ItemActiveInCity
(ItemId, CityId)

---

As to 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?