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 | State | Region | Street | Target | Achieved | 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,.....