Reflexive table and the usual message : You can not add or change record because... (1 Viewer)

I would still use a separate record to represent an unknown parent. This makes the selection of the unknown parent explicit rather than potentially an entry error.

I would also add a record validation that the ParentID cannot be the same as the ID. Obviously this would need to be added after the unknown parent self referenced entry was inserted

Click to expand...

If it makes you feel good go for it. I have never had the need but I do not see it hurt anything. I would just have to change how the recursive calls kick out. I have done a ton of large recursive databases (organizations, personnel and equipment, data models, engineering components) with 10s of thousands of nodes going hundreds of levels deep. To me this discussion is somewhat academic anyways, because in the applications I build this would never be an issue because the user would never manually input an FK. Normally if it is reflexive I am using a Treeview, and have no concern about the user worrying about FKs. If they drop a node on the main branch it gets a self referncing key. If they enter or drag it to an existing node then they get the PK from the parent node. This will always be transparent to the user. To create a node you either find a parent click on it and get the pop up to enter a child. To create a top level node you click on the top level. If somehow a mistake was made and it was not supposed to be a top level node, it will be clearly visible and you would just drag it to where it needs to go or select a parent from a list. However, what is very important is referential integrity. Having an orphan in a normal table is not a huge deal, in a recursive build it can be huge. Since you are normally doing recursive loading of the tree, one bad FK could cause thousands of relations to be lost in the recursion (break a branch).

For example the Treeview demo I did for Inflights bird breeding program that could show all offspring or all parents.

Normally I am using something like this to enter records.

MajP said:

For example the Treeview demo I did for Inflights bird breeding program that could show all offspring or all parents.

Click to expand...

A tree view seems the perfect way to display birds.

Ho ho

Sorry for the digression or for branching off from the main discussion again.

Finaly we decided to change the cardinalities. It means a stallion could have or not a father, in fact regarding the context it is logical. Hoiwever this exercise and question could be useful for others that met, meet, or will met this problem in Microsoft Access. It is useful to keep this post in your database.
I want to say thx very much to you all for your help, finally U did help us to see the reality of the problem.
Regards.