Let's say I have two tables that are related in that one table contains a column that is a key to the other table. In other words, it would be a 1:1 or 1:* depending on constraints.
Let's call it an Customer/Orders relationship to give it some context.
However, let's say the requirement is that there be no referential integrity because the table needs to be archived without affecting the rest of the system. In our case, they want to archive the customers, but leave the orders in the system.
Ignoring the fact we now have dangling references, which are just a fact of life when you need to prune systems in this way. What would you call this kind of "loose" relationship where there is no actual FK?
How might you illustrate this in an ERD without implying there is a FK? But still wishing to show that there is a relationship?
I realize that the proper method might be to use an FK anyways, and null the reference when archiving the customer, but that adds extra complexity they don't want to deal with. Further, they don't want to update the orders table after it has been finalized. They want to be able to find that customer number and go back in the archive and find the customer if need be.
Best Answer
It is still a foreign key, because it's a feature of your data. However, it is not enforced by a referential integrity constraint.
In my view this would mean that the foreign key would be present in the logical model ERD, but absent in the physical model.