CODEWITHSUNDEEP
mysqldatabasedatabase-designschema
Rajasekar
Rajasekar

Reputation: 18948

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

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

Upvotes: 0

Views: 524

Answers (2)

Aaronaught
Aaronaught

Reputation: 122624

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

Upvotes: 5

keyboardP
keyboardP

Reputation: 69362

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...

Upvotes: 0

Related Questions