CODEWITHSUNDEEP
sqldatabasedatabase-design
Maheep
Maheep

Reputation: 5605

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

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

Answers (3)

Yusufm.Salh
Yusufm.Salh

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

ypercubeᵀᴹ
ypercubeᵀᴹ

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

Oded
Oded

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

Related Questions