CODEWITHSUNDEEP
sql
user3452210
user3452210

Reputation: 147

SqL Design for Countries - States - Cities

Hi I am developing a Simple Form with HTML drop downs for Countires, States , Cities. Adding Countries , States , Cities and updating them is done , no issues with that. Every thing working perfect . And the Problem comes here at the time of deleting(Let's say deactivating).

I have designed the Tables which are ideal for this type of Country ,state, city drop down by referring some sites in net. Like:

Countries Table:

   |SerialNo|CountryName|CountryId|Active|

States Table
   |SerialNo|StateName|StateId|CountryId|Active|


CitiesTable:
   |SerialNo|CityName|CityId|StateId|IsActive|

So at the time of deactivating any Country i am making Active="false", so deactivated Countries are not going to display in my grid. If do like this The States which are in that respective Countries should also become deactivate right.

So i did like this: SQL Query:

Update CountriesTable set Active='false' where CountryId= @CountryId
Update StatesTable set Active='false' where CountryId= @CountryId

As i have CountryId in both tables i could deactivate Countries,States in two tables.But how could i deactivate the Cities of Deactivated States?? There is a concept called Triggers in Sql Which updates other table after Data manipulating in a particular table , can i achieve my requirement with Triggers, or is there any good way to achieve it?

How could i achive it?

I dont want to add one more column of CountryId to the citiestable Sorry for my bad english. Hope i am clear to all of you!! Any references , any Sql queries , any help much appreciated!!!

Upvotes: 0

Views: 1380

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 755094

This is a basic UPDATE statement to set the IsActive column of the correct rows in the Cities table for a given country ID.

UPDATE Cities
   SET IsActive = 'false'
 WHERE StateID IN (SELECT StateID FROM States WHERE CountryID = @CountryID);

It will work in pretty much any SQL DBMS. There may be other notations using join notations that would allow you to write it differently.

You'll have to incorporate that into a trigger for your DBMS using the relevant syntax for your DBMS. Since you didn't identify which DBMS, we can't be sure (though, as I noted, there's a fair chance that it MS SQL Server).

Or you can redesign the schema and queries so that setting the country's IsActive state to false is sufficient to ensure that states or cities from the country are not shown — as advocated by Mitch Wheat in his answer.

Note that your trigger should account for countries becoming active again; using 'false' will not always be appropriate.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300827

A trigger is not required. When selecting States, join to Countries and filter on Country IsActive

SELECT * 
FROM Countries 
WHERE IsActive <> False;

SELECT s.*
FROM States s
INNER JOIN Countries c on c.CountryId = s.CountryId
WHERE c.IsActive <> False;

Upvotes: 1

Related Questions