Danish Habib

Danish Habib

  • NA
  • 694
  • 244k

what tables should i create for below description

Feb 24 2016 5:01 AM
I have a web application in which i have users and each user is associated with locations and the locations are in hierarchy like
Country ----------Contains-----> Cities
 
Cities ----------Contains-----> States
 
 
States----------Contains-----> Regions
 
 
 
Regions----------Contains-----> Streets 
 
 
Now in my application i have Questions and dynamically i am assigning these question to users , each question has a target value which is set by admin and user have to reply and give the achieved value of this target value just like that
Q: Number of schools established ( target =10)
Answer (Achieved =5)
 
 but I want to assign this target based on location for example if a user ABC has Assigned 2 Countries , 3 States,4 Regions, and 12 Street then i want to give the target individually like I can give the target value for one district , one State,one Region,One street at a time ,
For example
Q:Number of schools Established ( target=3) for Country ("ABC")
then user must have to answer like give the achieved value i.e. from these three how much he made for this country
Answer: Achieved(1) for Country ("ABC")
 
 
How to manage this business logic can anyone tell me how much table i should create and what would be the relationship should i create a single table
Question Table 
 Question_ID User_ID(to whom this question is assigned) Country   StateRegion Street  TargetAchieved  date_of_submission
 1 1 1 Null Null Null 12 1 2/2/2001
       
         
         
         
         
 in the above table i am setting the target of Question ID =1 for Country_ID =1 so I am putting the nulls into the state , region and street columns .
 
 
Tell me either This table structure is true .
thanks in advance for looking this deeper issue which i am facing,..... 
 
 
 
 
 
 

Answers (1)