Database Design Question

Question

I have Users into my web application and each user has these fields
1-Name 2- Email 3-Password 4-Security Question 5-Security Answer 6-District 7-Tehsil 8-UCS 9-Villages

The Rules are as follows
The District have Tehsils and the tehsil have UCs and the UCs have villages they are cascade in nature

and each user can have many district or one district,IF one district then due to cascade he may have one or more tehsil and due to tehsil cascade he may have one or more UCs and due to cascade UCs he may have one or more villages .

What is the database design any example please share and how to save and show records in edit mode assume that we have a checkboxlist control for all these four entities Please responds

Danish Habib · Answer

Dear Suraj Sahoo Thanks for your reply I got what you said I want to let you know that I have made separate table for Districts for Tehsil for UCs and for villages District((P)districtidDname) Tehsil(districtID(F)TehsilID(P)TName) UCS(DistrictID(f)TEhsilID(F)UCSID(P)UNAME) VILLAGES(DistrictID(F)TEhsilID(F)UCSID(F)VillageID(P)VNAME) They are in separate table and i am now populating the records based on cascade option my question is that while storing the one user record what would be the database design mean a user can have assign many districts or one district and may b one or many tehsil and then may be one or many ucs and then may b assign one or many villages how to save that record not the individual District tehsil ucs village for populating .. I am asking about when the user save the record how his associated District tehsil ucs villages save

Suraj Sahoo · Answer

Hello Danish Here in your query it is the game and magic of the proper use of Foreign Key constraints as you are dealing with one to many relationship and also many to many relationships. First of all for loose coupling on the DB level separately have different tables. Like for example District will be one table with its own primary key and User tables's Primary key as its foreign key. Similarly Tehsils will have DistricID as its foreign key and UCs will have Tehsils and DisrictID as its foreign key. I hope you understand. Please post back your queries if any. Thanks