How to store multiple items in the database. Confused with the structure of the database


I had a select box where the user will select the list of cities they travelled. After that the admin will search for the users with particular cities.

I am very confused with the structure of the database. There are totally 300 cities. Creating column for each city and store it with boolean on or off, looks a childish technique. Any one help me plz

Not sure why you've tagged this both mysql and sql-server - are you using both?

Anyway, this is a standard many-to-many mapping:

Table: User

  • UserID (int, PK)
  • UserName (varchar(50), not null)

Table: City

  • CityID (int, PK)
  • CityName (varchar(50), not null)

Table: UserCity

  • AssociationID (int, PK)
  • UserID (int, FK User, not null)
  • CityID (int, FK City, not null)

To retrieve all of the cities for a given user:

SELECT c.CityID, c.CityName
FROM User u
INNER JOIN UserCity uc
    ON uc.UserID = u.UserID
    ON c.CityID = uc.CityID
WHERE u.UserID = @UserID