I realize the necessity to have referential integrity for restricting specific values on entry or even stopping them from removal upon a request of deletion. However, I'm unclear regarding a legitimate use situation which may exclude this mechanism from always getting used.

I suppose this could fall under several sub-questions:

  1. Just when was referential integrity not appropriate?
  2. Could it be appropriate to possess fields that contains multiple and/or even incomplete subsets of the foreign key's list?
  3. Typically, if this should be considered a schema structure design decision or perhaps an interface design decision? (Or even neither or both)

Ideas?

Just when was referential integrity not appropriate?

Referential intergrity if typically not utilized on Data Warehouses in which the information is a read only copy of the transactional datbase. Another illustration of when you'd not require RI is when you wish to log information including row ids maintaining referential integrity for any read-only log table is a total waste of database overhead.

Could it be appropriate to possess fields that contains multiple and/or even incomplete subsets of the foreign key's list?

Sometimes you care much more about taking data than data quality. Imagine you're aggregating a lot of data from disparate systems which each themselves are afflicted by data quality issues. Sometimes you're following the greater good of information quality and getting everything in one location despite damaged secrets etc. signifies a beginning point for moving towards true data quality. It isn't ideal, however it does happen because the beenfits could over-shadow the tradeoffs.

Typically, if this should be considered a schema structure design decision or perhaps an interface design decision? (Or even neither or both)

Everything about systems development is focused on information security, along with a key factor of that's data integrity. The database structure should lean towards enforcing this stuff whenever possible, nevertheless, you frequently aren't coping with modern database systems. Sometimes your computer data source is definitely an old-fashioned AS400 with lengthy-old applications. Sometimes you need to develop a data and business layer which offer for data integrity.

Just my ideas.

The only real situation I have come across is if you are planning to load a huge quantity of data to your database for the reason that situation, it might seem sensible to show referential integrity off, as lengthy you may already know for several the information is valid. When your loading/migration is finished, referential integrity ought to be switched back on.

You will find arguments about putting data validation rules in programming code versus. the database, and i believe it is dependent around the use cases of the software. If your single application may be the only road to the database, you can put validation in to the program itself and most likely be okay. But when a number of different programs are utilizing the database simultaneously (e.g. the application as well as your friend's application), you will want business rules within the database to ensure that your computer data is definitely valid.

By 'validation rules', I'm speaking about rules for example 'items in trolley > 0'. You might want validation rules. However I believe that primary/foreign secrets will always be important (or you might find afterwards that you want you'd them). I believe they're needed if you wish to do replication sooner or later.

Referential integrity would continually be appropriate whether it did not come at the expense of performance, scalability, and/or any other features.

In certain programs, referential integrity might be exchanged for some thing important than the standard from the data.

  1. Just when was referential integrity not appropriate?

    Sometimes when you're copying lots of records in large quantities, or rebuilding data from some kind of backup, it's easy to temporarily switch off the restrictions of referential integrity.

  2. Could it be appropriate to possess fields that contains multiple and/or even incomplete subsets of the foreign key's list?

    Copying data in by doing this goes against the idea of normalization. You will find are pros and cons for this approach.

  3. Typically, if this should be considered a schema structure design decision or perhaps an interface design decision? (Or even neither or both)

    I'd contemplate it a schema design decision. Consider the easiest way to model your condition in relational terms. Make use of the database in the manner it was intended.