Toad World® Forums

ER diagram self-reference relationship


#1

I am trying to create a self-reference relationship in the ER diagrammer.
The table has ID and ParentID columns plus some other columns.
ParentID column should point to the ID column on the same table.

It looks like ER diagrammer doesn’t support such feature yet.
Please advise.

Regards,
Yitzhak


#2

No. The ER Diagram does not do this.
Can you provide the usecase where you would want this?
Mainly the relations would be for joinging to columns.

Debbie


#3

To model the organizational chart:

employeeID managerID employeeName title

1 NULL Pete Sampras CEO
2 1 Rodger Federer COO
3 1 Nadal CFO
4 2 Haas manager
5 2 Davidenko manager
6 3 Lopez superintendant
etc.

So in this particular case the managerID column is linked to the employeeID column.

Many other scenarios could be model in the similar fashion.

Regards,
Yitzhak


#4

What database type are you connected to? In Oracle I would use a connect to prior in my select statement.


#5

It is Microsoft Access 2003 database.

Regards,
Yitzhak


#6

I created CR0224885 for this enhancement.


#7

Thanks.
It is a much needed but missing feature.

Regards,
Yitzhak


#8

Hi Ykhabins

You can’t create a self reference in ER and already add the attribute parent_ID as well
You have to delete child attribute (parent_ID) and create the relationship.
you have to set min and max cardinality many to one or many to zero depend if the child attribute is mandatory or not.
in this case you can create the self-reference
and when you will generate the RS (relation schema) a column or more (depend from how many primary keys are on your table)will be added and it will be related with corresponding column.

Hope it was helpfull and clear for you.

Regards
Lulzim