Design a database for a hierarchical data like country, state, zone, street etc., The tags and length of the hierarchy are not specific and they can change anytime ?



Hierarchical data

    • Hierarchical data is a common relational data pattern for representing tree-like data structures, such as an organizational structure, a project breakdown list, or even a family tree.
    • In relational database model, One thing to remember is that flexibility in keys.
    • We have the flexibility to design the database, it might be worth having multiple geometry types for certain data types.
    • For example, having a city represented as a polygon would be useful to perform spatial queries to determine all points of interest within that city.
    • At the same time, it is oftentimes more desirable to represent a city as a point on a map. The database allows you to do this by separating the attribute data from the features into their own table, and linking them together with a Primary Key/Foreign Key structure.
     Hierarchical data

    Hierarchical data

    Primary Key:

      • A Primary Key is an identifier for a record in a table.

      Foreign Key:

        • A Foreign Key is a reference to that same identifier in a different table, thus linking them together.

          CREATE DATABASE testDB:

            • We want to includes this tables to the database:
            • Continent - Fields - Geom (Polygon), ID(Primary Key), Name, Etc
            CREATE TABLE Continent (
                ID int NOT NULL PRIMARY KEY,
                Name varchar(255) NOT NULL,
                Geom_Polygon varchar(255),
                Age int
            );
            
            • Country - Fields - Geom (Polygon), ID, Name, Continent ID(Foreign Key)
            CREATE TABLE Country (
                ID int NOT NULL PRIMARY KEY,
                Name varchar(255) NOT NULL,
                Geom_Polygon varchar(255),
                Continent_id int FOREIGN KEY REFERENCES Continent (ID)
            );
            

            Region:

              • If there are different levels of regions within a country, then we would have them as different layers.
              • So, if a country has regions called districts, and a number of districts combine to form a state, which then combine to form the country, you would have a layer for each type.
              • Reg_State - Fields - Geom (Polygon), ID (Primary Key), Name, Country ID, Etc
              CREATE TABLE Reg_State (
                  ID int NOT NULL PRIMARY KEY,
                  Name varchar(255) NOT NULL,
                  Geom_Polygon varchar(255),
                  Country_id int FOREIGN KEY REFERENCES Country(ID)
              );
              

              Cities:

                • Have a table that contains attributes for each city, but no geometry.
                • Have geometry tables with different geometries, like point or polygon, that link to the main city table.
                • City_Info - Fields - CityID (Primary key), Name,Reg_District ID (Foreign Key), Etc,
                CREATE TABLE City_Info (
                    CityID int NOT NULL PRIMARY KEY,
                    Name varchar(255) NOT NULL,
                    Reg_District_id int FOREIGN KEY REFERENCES District(ID)
                );
                
                • City_Pt - Fields - Geom (Point), ID, Name, CityID (Foreign Key)
                CREATE TABLE City_Pt (
                    ID int NOT NULL,
                    Name varchar(255) NOT NULL,
                    Geom_Point varchar(255),
                    CityID int FOREIGN KEY REFERENCES City_Info(CityID )
                );
                
                • City_Poly - Fields - Geom (Polygon), ID, Name, CityID (Foreign Key)
                CREATE TABLE City_Poly (
                    ID int NOT NULL,
                    Name varchar(255) NOT NULL,
                    Geom_Polygon varchar(255),
                    CityID int FOREIGN KEY REFERENCES City_Info(CityID )
                );
                
                • This is the one of the ways of setting up the database, again it depends on your specific needs for being able to link different pieces of data together.
                • Once the table structure is determined, the next part will be to set up queries to retrieve the information you want, for inclusion in a report or some sort of viewing application.

                Related Searches to Design a database for a hierarchical data like country, state, zone, street etc., The tags and length of the hierarchy are not specific and they can change anytime ?