I am creating a design for different Geographical types where each type is just a little "place holder" in over all heirarchy.
One example is
Zip code Belongs to Territory Belongs to District
Other would be
Zip code belongs to State belongs to Some Region
We have two schools of thought one is that:
1 create different tables like Zipcode, Territory, Regions etc
- other is that just create one table which is called Geographical Entity and then everything is just a type so you can assign a Type Id to each geographical entity and self reference the table
Now, if I go by design #1 then I don't have to worry about any non traditional decision. I can create one class in OO space and then map to each table. Also, imposing business level rules such as Territory can do such and such things but Distrtict can not. And also things like a Territory can belong to District but District can not belong to Territory are all contained in design itself.
However, If I go to Design #2. It is extensible such that if we have a new Geographical type called "XYZ" then we do not need to add a new table for that. We can simply add a new type into Geography type and it would be addressed.
I find Design #2 extensible but unnecessarily complex. Am I right thinking it that way? Or that's the way to go?