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

score 8 · Accepted Answer · answered Jan 10 '12 at 09:26

8

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)

answered Jan 10 '12 at 09:26

Oded

489,969
99
883
1,009

This is good. But why I wanted to have them in same table was for speed in search operation. As many of the searches will be using cities. – Maheep Jan 10 '12 at 09:31
4

@Maheep - You have a misconception here. Having them in one field will be _slower_, as you will need to parse it, order it and still join on the results. Having it in a separate table is the correct relational design. When it comes to speed - you need to measure and find bottlenecks, not guess at where they may happen. – Oded Jan 10 '12 at 09:33

score 4 · Answer 2 · edited May 23 '17 at 12:17

4

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?

edited May 23 '17 at 12:17

Community

1
1

answered Jan 10 '12 at 09:27

ypercubeᵀᴹ

113,259
19
174
235

This is good. But why I wanted to have them in same table was for speed in search operation. As many of the searches will be using cities. – Maheep Jan 10 '12 at 09:32
1

Read the answer by Bill Karwin in the link I added in the answer. – ypercubeᵀᴹ Jan 10 '12 at 09:52

score 0 · Answer 3 · edited Apr 30 '17 at 11:24

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.

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

3 Answers3