I have a web application in which i have users and while creating users i have locations and these location are in hierarchical pattern just like Country {contains} Cities,
Citites{contains} States,
States{contains} regions
same as according to my system i have
District ----------{contains 1...*}------------------------------------->tehsils
tehsils----------{contains 1...*}------------------------------------->UCS
UCS----------{contains 1...*}------------------------------------->Villages
I can assign a single user to one or many district , one or many Tehsils,one or many UCs,one or many Villages
so Top Level entity is District than Tehsil than UC than Villages i am doing this all in my application no worry :).
Now I have Question in my application and it is just like a quiz engine , I am assigning Question to each user through my system so each question also having Target value just like
Q#01: Number of schools Constructed (Target =12) {for District = "ABC}
Answer:Achived (10)
Now I want to ask you a db Design how much table i have to create and the relationship between them
I want to assign the target value based on location like one question can have a Target value based on District , or Tehsil, OR UC,OR Village but not all at the same time so user must answer the question relatively like answer achieved value for target value for District "ABC"
can i make a single table just like
Table(Question_ID,UserID,District,Tehsil,UC,Village,Target,Achieved)
it means it will insert the target vs achieved value based on location if district is selected then it will insert just like that)
Table(Question_ID,UserID,District,Tehsil,UC,Village,Target,Achieved)
VALUES(1,2,'ABC',NULL,NULL,NULL,10,1)
putting the NULLS for other location is it ok ?
I need assistance thanks in advance ... :)
What I have tried:
I have made a simple Table just like that
Table(Question_ID,UserID,District,Tehsil,UC,Village,Target,Achieved)