DB Designig for City details with multiple webservices

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » DB Designig for City details with multiple...

DB Designig for City details with multiple webservices

Rate Topic

Display Mode

Topic Options

Author

Message

SQL006

Posted Thursday, February 07, 2013 3:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:33 AM
Points: 161, Visits: 865

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

Post #1416923

« Prev Topic | Next Topic »

Permissions