Hello, I'm a beginner in database design.
I've to design an Address table. I always design it as State(stateid) -> District(districtid) references State -> City(cityid) references District -> Address(addressid) references City.
But now the problem is, I've to take up villages into consideration. Some people live in City and some in Village. So how to design this?
I've taken two extra tables, Block(blockid) references District and Panchayat(panchayatid) references Block.
But in Address table now I've to take two foreign key columns one which references City and one which references Panchayat. If there is a value in City column the Panchayat Column will be NULL and vice versa. This way I can see who live in village and who lives in city.
Is this design correct or not? Or I should follow a different approach. Please help.