Topic: Self table reference AKA self referencing relationship

Hi all, I am struggling with a very simple case that basically MyVisualdatabase was not built to handle: self referencing relationship: using already existing data in the same table to be used as an entry.

In my attached example, I would like to select the manager (who is supposed to be an existing employee) as "name firstname" and save it as an "id" from the employee table in the "manager" column without writing an SQL query.

Is this possible?

It looks like when using a self referencing auto query, it uses the table name as a column name (?)

Post's attachments

Attachment icon Self-table_reference.zip 336.48 kb, 32 downloads since 2023-11-06 

Re: Self table reference AKA self referencing relationship

Well... after a brain storm with myself, I managed to do something that does the job, if someone has a better idea, by any mean, you are welcome.

Post's attachments

Attachment icon Self-table_reference (2).zip 337.15 kb, 38 downloads since 2023-11-06 

Re: Self table reference AKA self referencing relationship

You can do without a script and an additional component, but you will need to fulfill a number of conventions.
1. The field that stores a link to the table should be called id_<table name>
2. When setting up a combobox, the ForeingKey property must be typed from the keyboard: <table name>.id_<table name>

Post's attachments

Attachment icon Self-table_reference.rar 293.69 kb, 53 downloads since 2023-11-07 

Визуальное программирование: блог и библиотека.

4 (edited by k245 2023-11-07 07:28:33)

Re: Self table reference AKA self referencing relationship

By the way, if you remove the mandatory flag from the id_employees field, the table can be displayed as a tree. A tree must have at least one element whose parent reference is NULL.
https://myvisualdatabase.com/forum/misc.php?action=pun_attachment&amp;item=10052&amp;download=0

Post's attachments

Attachment icon изображение_2023-11-07_102802859.png 4.03 kb, 14 downloads since 2023-11-07 

Визуальное программирование: блог и библиотека.

Re: Self table reference AKA self referencing relationship

Hi Thierry, Konstantin,

The field that stores a link to the table should be called id_<table name>

Manually adding a field to the table and calling it 'id_[tablename]' to force a relationship back to itself is very neat - and so simple (but then, everything is simple once someone first thinks of it and shows you how!).
I've added this to my list of useful tricks.
Thanks,
Derek.

Re: Self table reference AKA self referencing relationship

Thanks a lot K245!! That is a trick worth knowing and it saves a lot of headaches.

I agree that with this method, the "not null" constraint must be removed, it was set to work with my workaround, but if you remove a manager from someone at some point, you get an error because of the "not null" constraint.

7 (edited by k245 2023-11-08 06:54:55)

Re: Self table reference AKA self referencing relationship

tcoton wrote:

Thanks a lot K245!! That is a trick worth knowing and it saves a lot of headaches.

I agree that with this method, the "not null" constraint must be removed, it was set to work with my workaround, but if you remove a manager from someone at some point, you get an error because of the "not null" constraint.

In this case, it is necessary to add a check before deleting an entry: if the entry being deleted is referenced as a manager, then report this and not allow deletion.


There may be several options: 1) do not delete; 2) reset the link in dependent records to NULL; 3) delete along with dependent records. In your case, the first or second option will be suitable.


In real systems, records are rarely deleted unless they were entered to test the system. Typically, flags are used to indicate that the entry is being archived and should not be displayed in normal cases.


But your case is even more interesting. I would add a separate table - managers, which would link to the employees table. In this case, when dismissing or changing a manager, you do not need to edit all the employees who linked to him, but rather make one edit in the manager table, linking it with another person or with a special record, where instead of the first and last name it will be written “Position vacant”

Визуальное программирование: блог и библиотека.

Re: Self table reference AKA self referencing relationship

Yeah but that is not a good practice database design, having redundant data!!

The best would be, in case of "not null" constraint, to set the value to 0 or whatever value is set by default for "not null" instead of "null" when removing a manager from an employee hierarchy. That cannot be done without a script since the default action of Myvisualdatabase is to set a field to "null" when deleting.

Re: Self table reference AKA self referencing relationship

tcoton wrote:

Yeah but that is not a good practice database design, having redundant data!!

The best would be, in case of "not null" constraint, to set the value to 0 or whatever value is set by default for "not null" instead of "null" when removing a manager from an employee hierarchy. That cannot be done without a script since the default action of Myvisualdatabase is to set a field to "null" when deleting.

I agree, but if a tree-like hierarchy is required, then you cannot do without the NULL value in the reference to the parent of the root elements.

Визуальное программирование: блог и библиотека.