Database Self Relationship (Relationship between a desk and the desk itself)

Database Self Relationship (Relationship between a desk and the desk itself)

Within the earlier article, I shared the best way to design and create an ERD (Entity Relationship Diagram). In that article, we mentioned relationships between tables. It seems that other than the connection between tables, there may be additionally a relationship between the desk and the desk or what may be known as a self relationship.

Self relationships are superior materials from database modeling materials, and I occurred to get this materials once I was learning databases utilizing studying modules from Oracle. This self-relation methodology may be utilized in all DBMS (Database Administration Methods). On this tutorial I’ll use MySQL for sensible implementation.

What’s Self Relationship in Database Modeling?

Self Relationship is a relationship {that a} desk has that’s linked to the desk itself, so a main key in a desk has a relationship with a international key in that desk.

  • one-to-many (1-n), which implies that a knowledge in a desk influences quite a lot of knowledge in different tables. Within the self-relation idea, 1 knowledge in a single desk may be associated to many knowledge in the identical desk.
  • one-to-one (1-1), is a relationship between tables if knowledge in a single desk can solely be associated to 1 knowledge in one other desk. Within the self-relation idea, 1 knowledge in a single desk may be associated to 1 knowledge in the identical desk.

For the diploma of cardinality, many-to-many (nn) can’t be carried out on tables that use relations within the type of self-relations as a result of principally many-to-many has the impact of making a brand new entity/desk which can have totally different attribute contents so it can’t be carried out.

If a desk within the database applies a self-relationship with a cardinality diploma of 1 to many, it’s attainable that the info in that desk can have a hierarchical kind. A hierarchy is an association of a sure set of objects/knowledge that’s grouped based mostly on ranges.

Why is Self Relation Carried out in Databases?

The aim of implementing Self Relations is to reduce using tables within the database, in different phrases, to simplify using tables. Minimizing the variety of tables can have an efficient influence in writing program supply code.

For instance, there are 2 tables, particularly the Worker Desk and the Supervisor Desk, which have the identical attributes to allow them to be simplified into 1 desk, combining relationships and offering an enum() knowledge sort for the employee_position attribute.

Picture of Supervisor Desk and Worker Desk Design

(*~which will probably be simplified)

The next is an image of two tables which have been simplified into one desk, particularly the worker desk. Utilizing Self Relations within the worker desk makes it as if we’re utilizing 2 tables.

Worker Desk Design Picture
(implementation of self relations)

If there are 2 worker and supervisor tables, then we have to write 2 CRUD applications to entry and manipulate every desk.

Often, when a system analyst/programmer applies self-relationships to a number of tables within the database that will probably be designed, they have to be recognized from the beginning, or if the appliance is already working, then it will likely be modified, which is able to waste time to repair it.

Instance of Implementing Self Relationship in Database Tables

Using self relationships may be utilized in a number of circumstances when creating database tables. The next circumstances may be utilized:

  • Relations desk,
  • Desk of workers / workers in an organization,
  • Metric and workplace desks in cupboard association,
  • Member desk on the group chart,
  • Person desk in multi-level advertising,
  • and so forth

Draw knowledge on the Worker Desk

As defined above, tables which have self-relationships have knowledge in hierarchical kind. On this manner, the info within the worker desk above may be interpreted in chart kind:

Hierarchical picture of knowledge within the Worker Desk

Learn how to Implement Self Relationship in a Database

The way in which to implement a self relationship in a database is by connecting the ID attribute as a main key with one other ID attribute as a international key.

On this tutorial I’ll use the worker desk for the corporate database for instance. The next are the steps for making a desk that makes use of self relations in MYSQL dbms:

  1. Please open XAMPP first, then run Apache and MySQL.

  2. Then open your net browser then open phpmyadmin: localhost/phpmyadmin.
    Choose the SQL Tab on the navigation bar to open the SQL Executor function:

    Web page Picture When the SQL Tab is energetic in PHPMYADMIN

  3. Create a brand new database with the identify “company_db”, utilizing the next supply code:

    CREATE DATABASE company_db;

    Please copy the code above then paste it within the SQL editor on the PHPMYADMIN web page above.

    Picture of Operating the Command to create a database in PHPMYADMIN

  4. Create a desk with the identify “workers”, then please copy the code under then execute it.

  5. Create a constraint between employee_id and manager_id to create a self relationship on the worker desk. Please use the supply code under then execute it to create a relationship between tables.

    Picture of Desk Outcomes with Self Relation

  6. Insert supervisor and worker knowledge within the worker desk. Please copy the supply code under then execute it in your MYSQL

Following are the outcomes of the info returned with PHP in JSON kind, so the info can appear to be a hierarchy:


  1. The supply code above can be utilized in DBMS aside from MySQL as a result of principally the DBMS executes instructions within the type of a question language.
  2. To make use of the supply code above, please alter the table_name, attribute_name, data_type, and so forth. to your specialty or undertaking that you’re engaged on.

That is the reason relating to the reason and software of self relationships in database desk design that I’ve written on this article, I apologize for a lot of shortcomings.

~Thank You~

~Hopefully that is helpful~

Share This


Wordpress (0)
Disqus ( )