Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have two tables that are related in that one table contains a field 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.

share|improve this question

2 Answers 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.

share|improve this answer
    
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 '13 at 12:05

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.

share|improve this answer
    
How do you indicate logical vs physical in an ERD? –  Erik Funkenbusch Sep 13 '13 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 '13 at 17:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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