February 26, 2008 at 10:25 pm
Hi All,
I am really confused on this issue, I think, I may not clear about the design of data thats why i am getting problem.
Let me explain the issue.
I have a web service, which giving me Region based data like
1- Region Name
2- City Present under Region
3- Area present under City.
But the format is really problem for me.
Region
--City1
---Area1
---Area2
---Area3
---Area4
--City2
---Area1
---Area2
---Area3
---Area4
If you have a clear look on the sample data, Only you find the Hyphen (-) is marking Type of the Region, City and Area.
What is my job, I have to store the data into my Table named as RegionDetails
Here I can achieve 2 ways,
--------------------------
1st : I can go for 3 sub table like - RegionMaster, CityMaster & Areamaster and join all to get the data.
2nd : But I wants, If I can able to store the Download data in a Varchar column.
But here the issue is how can i find out which one is area, which one is city and which one is Region.
By reading the hyphen(-) can solve my problem.
but lets say if I wants to search from City1, it should include all the Area belongs to that city.
I think I need to add 3 more bit column to make difference between region, city and area. Am I rite?
Please give me the idea for this type of problem.
Cheers!
Sandy.
--
February 27, 2008 at 2:56 am
So this 'list' with hyphens and stuff, this is your sourcedata?
Can there be more than one region in this list?
Is your purpose to take this 'list' and store it in your own model's table(s) in some way?
/Kenneth
February 27, 2008 at 4:34 am
Hi Kenneth,
Kenneth
----------
So this 'list' with hyphens and stuff, this is your sourcedata?
Ans: Yes, this is the Data coming from Webservice.
Kenneth
----------
Can there be more than one region in this list?
Ans: Nope, Only one Region is present.
Kenneth
----------
Is your purpose to take this 'list' and store it in your own model's table(s) in some way?
Reply: Yes, My requirement is here, what should be the design of the Table or how to make more efficient way to get the data in this way for search too.
Cheers!
Sandy.
--
February 27, 2008 at 7:37 am
Hmm well, I see some definitive issues here...
It's on one hand no problem to identify what is a Region, City or Area, just count the number of hyphens.. But...
How would one tell if Area1 belongs to City1 or City2 in the hierarchy..?
The only way I see it for the list provided, is that you need to maintain the order of the presentation, index each row somehow without upsetting the order, and then traverse the list and process them in sequence.
Else I see nothing that would 'sort' the different Areas under their respective Cities.
/Kenneth
February 27, 2008 at 10:32 pm
Hey Kenneth,
Now I am following this way.
I have added one more column as RegionType.
If it is Region then it is 0
---
If it is City 1 then it is 1
and all the area belongs to that city is 1
---
If it is City 2 then it is 2
and all the area belongs to that city - 2 is 1
----
In my Procedure I am getting from the user the Region Type
like if user select as region it will be 0,
if city, then it will get the Regiontype, and searching as per Region Type.
if he select area, then it will directly take regioncode for search.
Now table design
-----------------
RegionCode RegionName RegionType
======== ========= =========
1 MainRegion 0
2 - City 1 1
3 -- Area 1 1
4 -- Area 2 1
5 - City 2 2
6 -- Area 1 2
7 -- Area 2 2
Am I right???
Cheers!
Sandy.
--
February 28, 2008 at 3:30 am
Well, you may be on the right track.
What you have is three entities.
Region, City and Area.
You need an id for each.
RegionId, CityId, AreaId.
One region can have one to many Cities.
One City can have one to many Areas.
You need to keep track of the relationships between the tree, so you don't accidentally mix up an area with the wrong city and so on...
A good way to start is to sketch on a paper with a pen, draw boxes and lines and see how it will fit together according to what your needs are and what it should be used for.
/Kenneth
February 28, 2008 at 4:00 am
hi,
now it is working fine.
Cheers!
Sandy.
--
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply