Question 221560 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to do a Multi-level Relationships ? (Multiple Table relation in one column)

I have a postgresql database with four tables: User, Country, City, and Street. The User table contains information about users, including their name, level (which can be "Country", "City", or "Street"), and ids that corresponds to the ID of the "Country", "City", and "Street" they belong to.

Table Structure:

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  level VARCHAR(10) NOT NULL, --ENUM('Country', 'City', 'Street')
  id_country INT NULL,
  id_city INT NULL,
  id_street INT NULL,
  FOREIGN KEY (id_country) REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_city) REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_street) REFERENCES Street(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE Country (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL
);

CREATE TABLE City (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  id_country INT NOT NULL,
  FOREIGN KEY (id_country) REFERENCES Country(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE Street (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  id_city INT NOT NULL,
  FOREIGN KEY (id_city) REFERENCES City(id) ON UPDATE CASCADE ON DELETE CASCADE
);

I want to establish proper relationships between the User table and the other three tables, so that I can directly access the corresponding "Country", "City", or "Street" based on the user’s level. My current approach involves adding three separate columns (id_country, id_city, id_street) and three join operations for each case, which seems repetitive and not an ideal design.

An example of my use case is:

switch (user.level) {
  case "Country":
    query = ' SELECT * from "user" AS u
              INNER JOIN country c ON u.id_country = c.id '
    break;

  case "City":
    query = ' SELECT * from "user" AS u
              INNER JOIN city c ON u.id_city = c.id '
    break;

  case "Street":
    query = ' SELECT * from "user" AS u
              INNER JOIN street s ON u.id_street = s.id '
    break;
}

Is there a more efficient and concise way to represent these relationships in the User table while maintaining data integrity and adhering to standard database design principles?

I got the idea to use a composite foreign key like this

CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  location_id INT NOT NULL,
  location_type VARCHAR(10) NOT NULL, --ENUM('Country', 'City', 'Street')
  FOREIGN KEY (location_id, location_type) REFERENCES Country(id, 'Country' )
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  FOREIGN KEY (location_id, location_type) REFERENCES City(id, 'City' )
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  FOREIGN KEY (location_id, location_type) REFERENCES Street(id, 'Street' )
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

However, SQL doesn’t accept a static value as a reference. I don’t have an advanced knowledge of SQL.

Any suggestions or alternative approaches to improve the database design would be highly appreciated. Thank you!

Tags:

2

Answers


  1. 0

    Your 3 tables (country, city, street) are correctly designed. But beware of CASCADE mode in FK and add IDENTITY on PK…

    Your main table is not properly designed and should be create as this :

    CREATE TABLE "user" (
  id INT PRIMARY KEY IDENTITY,
  name VARCHAR(255) NOT NULL,
  level VARCHAR(10) NOT NULL, 
  id_street INT NULL,
  FOREIGN KEY (id_street) REFERENCES Street(id) 
);

    Then add a view to complete :

    CREATE VIEW user_address
AS
SELECT u.id AS user_id,
       u.name AS user_name,   
       u.level,
       s.name AS street_name,
       ct.name AS city_name,
       cr.name AS country_name
FROM   "user" as u
       LEFT OUTER JOIN Street AS s ON u.street_id = s.id
          LEFT OUTER JOIN City AS ct ON s.city_id = ct.id
             LEFT OUTER JOIN Country AS cr ON ct.country_id = cr.id;
    Login or Signup to reply.
  2. 0

    Well, you do have the option of doing something like this:

    CREATE TABLE "user" (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  geo_entity_id INT NOT NULL,
  FOREIGN KEY (geo_entity_id) REFERENCES geo_entity(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE geo_entity (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code INT NOT NULL,
  level VARCHAR(10) NOT NULL, --ENUM('Country', 'City', 'Street')
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES geo_entity(id) ON UPDATE CASCADE ON DELETE CASCADE
);

    So user has a simple foreign key to a thing called geo_entity – which is for any kind of entity: a country, city, or street as the case may be. And a geo_entity row can have another geo_entity row as its parent.

    If you join user to geo_entity, and see what "level" is set to in geo_entity, that’ll tell you what level of user this is.

    If it helps you, you can also create country, city, and street views based on geo_entity.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search