Relation in same table

Hi I wanted to create a table called customer where the columns are CUST_ID - NAME - DATEOFBIRTH - ROLE -REPORT TO

cust_id being Primary Key
the role column will consist of either ‘director’, ‘manager’, ‘supervisor’ and ‘assistant’.
the report to column I want it to consist of only the cust_id numbers. So for example supervisor will report to manager, manage to director. But in that column id like it only to be cust_id.

Is there a way of link report to column to cust_id.?
Would I be on the right track if I read up on recursive relationships?

There are probably many ways this could be organised, the best depending on your exact requirements.
But this is looking like probably more than one table, for example, maybe “Roles” should be a table, where a person’s id is assigned to a role (or possibly another LUT if many to many role/people). Then in the original customer table, “report to” holds the role ID.

1 Like

yes and yes

CREATE TABLE customer
( cust_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, name VARCHAR(37)
, dateofbirth DATE
, role VARCHAR(37)
, report_to INTEGER NULL
, CONSTRAINT reports_to 
    FOREIGN KEY ( report_to )
    REFERENCES customer ( cust_id )
);
1 Like

yes, you can link the report to column by foreign key. by this you can link them.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.