DB Designig for City details with multiple webservices

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  February 7, 2013 at 3:26 am

  #270501

  I am working on travel application, so we have to deal with different web services like GTA, Gallileo, Kuoni etc . for getting information regarding Hotel details

  Each web service has its own list of city code and city name.

  I want to design a table to store the city details from different web service, after some research i came to this two approach

  1st approach

  CREATE TABLE [dbo].[City](

  [CityID] [int] NOT NULL,

  [CountryCode] [varchar](5) NOT NULL,

  [AppCityCode] [varchar](10) NOT NULL,

  [AppCityName] [varchar](200) NOT NULL,

  [GTACityCode] [varchar](10) NULL,

  [GTACityName] [varchar](200) NULL,

  [GWSCityCode] [varchar](10) NULL,

  [GWSCityName] [varchar](200) NULL,

  [KuoniCityCode] [varchar](10) NULL,

  ....

  ....

  ....

  ....

  ....

  ....

  )

  in this approach when ever a new webservice is added then two column (city code and city name) corresponding to the webservice added...due to this modification there will be change in stored procedure and in frontend application code.

  There will be no duplication while loading the cities in the textbox

  2nd Approach

  WSSupplier table is used to store Webservice details like GTA,Gallileo..

  CREATE TABLE [dbo].[WSSupplier](

  [SupplierID] [smallint] NOT NULL,

  [SupplierName] [varchar](100) NOT NULL

  )

  CREATE TABLE [dbo].[City](

  [CityID] [int] IDENTITY(1,1) NOT NULL,

  [AppCityCode] [varchar](20) NULL,

  [AppCityName] [varchar](150) NULL,

  [CountryCode] [varchar](10) NULL,

  [WSSupplierID] [smallint] NULL,

  [WSCityCode] [varchar](20) NULL,

  [WSCityName] [varchar](150) NULL

  )

  in the 2nd approach the cities will be added row by row with corresponding web service supplier ID

  if new webservice come then i don't have to modify the table structure or in frontend application

  while loading cities i have to use DISTINCT to load unique city in the textbox or dropdown in frontend

  In both approach i am using Appcitycode and Appcityname this will load the city textbox or dropdown in the application while selecting the Appcityname it will get the corresponding web service city code and send it as request to the webservice to search a hotel in a particular city.

  I want to know which will be the best approach or if there is any other good approach