Circular reference dangers

[SueBK]

I want to set up a table to enable users to search for government department name, which change on a regular basis. I would like to include "successor" field (possibly a predecessor, but I'm not sure).

My current fields are:
DeptID (autonumber)
Name
Jurisdiction
Abbreviation
Current (yes/no)
Successor

If [current] is "no", [successor] should have an entry. Can I reference that entry to another record in the same table without causing myself headaches? I know I can do it simply by making it a lookup field on the same table, but will that cause me grief down the track?

 

[Galaxiom]

It is quite normal to refer to another record in the same table. The lookup should be on the form. Just don't include it in the table field if you want to follow best practice.

However having both Current and Successor is a breach of normalization. The presence of a successor is sufficient to indicate the entry is not current. A Null means they are current. If the Department is completely deprecated then use an out of scope number.

Similarly the Predecessor. If you have both then these could be conflict between the two records. This always indicates normalization errors. The Predecessor can be found by using a self join query.

However have you considered what you woulddo if the Department is split into two? You only have space for one Successor. If you want to cover this then ue a junction table so there can be multiple records for the Successor.