February 7, 2013 at 3:26 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply