2

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • 1
    A foreign key may or may not be a feature of the data as given, before it's manipulated by the application prior to entry in the database. Foreign keys are a feature of the relational model of data, but not a feature of the entity-relationship model of data. If the ERD is being used to represent a relational schema, then foreign keys are germane. Otherwise, not.
    – Walter Mitty
    Sep 15, 2013 at 12:05
1

FK is not the only (even though preferable) way to enforce referential integrity. Triggers can be used as well - in your case you can validate integrity during inserts(updates are not allowed if I understood you well), and let deletes go through. It's not ideal, but it enforces integrity to some extent...

In any case , I'd display it as a logical relationship, not as physical.

Improve this answer
2
  • How do you indicate logical vs physical in an ERD?
    – Erik Funkenbusch
    Sep 13, 2013 at 17:00
  • Sorry, my bad, logical/physical is not applied to entity-relationship model. I meant having logical and physical data models. Then physical shows what exactly is enforced by the engine, and logical describes logical relationships.
    – a1ex07
    Sep 13, 2013 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.