set all entries to inactive when one is set to active

I have a list of cities, each city has a column 'active' (boolean). When I set one city to be active, I want all other cities to be inactive.

What is the best way to accomplish this?

I was thinking of another table that stores the active city. Do I need to use a singleton for that? If so, how do I link those two tables?

I would love to get some examples on how to solve my problem. Thank you! :-)

Edit: Right now I have one model: City

A city can have a name, a zip code and it can be activ开发者_JAVA百科e or inactive - just three columns.

I start filling the database with 5 cities.

When I set City #1 to be active, all other Cities should be inactive.

When I set City #2 to be active, all other cities should be inactive.

To accomplish this I see two different ways:

1) Set a filter that updates all other entries when I save a city.

2) Store the active/inactive state in a second table.

Option 2) looks like a cleaner way, a simple table with just one entry. But what does the model look like in this case?

I really appreciate your replies, guys. :-) Do you know these days when you feel stupid? I have one of those days. :-(

Option 2):

What I have is 'City' and 'ActiveCity'.

City has:

- title:string

- zipcode:integer

ActiveCity has:

- active:boolean

- city_id => references :City

City has_one :active_city

ActiveCity belongs_to :city

In my routes.rb there is:

resources :active_cities

resources :cities

The following works:

c = City.find(1)
ActiveCity.create(:city_id => c, :active => true)

When I have several cities, I can also do:

c2 = City.find(2)
ActiveCity.create(:city_id => c2, :active => true)

That means I have two active cities and that's now what I want.

What is necessary to have ONE active city at any time?

In option 1, everytime you want to change the active city you perform operations to every record in that table.

In option 2, everytime you want to change the active city, you change 1 record in the database.

You should go with option 2, not only for performance but because it makes sense logically.

The model in Option 2 should look like what Chirantan said in the comment to the last answer

If there can be only one active city at a time then maintain foreign key of the city in the users table (or whatever table for which the city may be active). Totally depends on your requirements though. More information on the problem domain would help.

You should go for option 2, but put a trigger on the table, so that you cannot insert more than one record.