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
sql-server - are you using both?
Anyway, this is a standard many-to-many mapping:
- UserID (int, PK)
- UserName (varchar(50), not null)
- CityID (int, PK)
- CityName (varchar(50), not null)
- 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