Reputation: 5605
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
Upvotes: 4
Views: 18148
Reputation: 39
All the words would say not recommended, as intersection of row/col is suppose to be one cell, so what if you made another column that represent that, for example if my project is a library and my task is to List the names of customers who have bought "Math for elementary students" AND "Explanation of Sahih Muslim" in a SINGLE order.
That means I need a list of books(IDS) in a single order.
solution :
Define a Column like the orderID (the primary key ), yet allows repeatition, that simply will allow you to combine those rows later.
Upvotes: 0
Reputation: 115660
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?
Upvotes: 4
Reputation: 499392
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)
Upvotes: 8