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

Question

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

Answer 1

Hi,

You don't have to create a column for each city. You simply create a "City" column, where each row is a city. You then have a "Visited" column, where each row is a boolean. In total, there are only two columns.

City      |   Visited

London    |     1
Paris     |     0 
New York  |     1

etc...

Answer 2

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
INNER JOIN City c
    ON c.CityID = uc.CityID
WHERE u.UserID = @UserID