Referential Integrity Good or Bad ??

[AndWy]

I'd heard that it is bad practise not to enforce referential integrity when adding relationships to tables. If this is so how do you get around the following scenario without setting up a dummy record in the Suppliers table??

eg You may have orders that require a record in a supplier's table but what if the user does not want to select a supplier on every occasion.

 

[MichaelRed]

There is trouble right here in "orders" city.

Somewhere, the database design isn't following the "model" of the process. "We" don't really know the business you are in, so there is a barrier to being able to give specific advice, so I'm not going to try.

For GENERAL ADVICE:

The process (application/database et al) should be more-or-less a "MODEL" of the business. In the case of requiring a supplier for an order, if the business does not require it, WHY would the application? conversly, if the business does require a supplier for the order, how could you even consider NOT requiring it on the order? Going to the next step, IF the supplier is required, don't you think ti should be a valid supplier? Isn't that what the relationship does?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[HenryAnthony]

I agree with Michael however, if you want to create the order and pick a supplier later you could enter a fake supplier such as "To be determined".

Michael, what would be the downside to this strategy?

Henry

 

[MichaelRed]

Henry,

The down side is - the 'real' supplier will not get filled in. If there is a problem with the order later on, you will not be able to trace back to the specific supplier.

My example - taken from a recent job.

The app was a 'distributior'. We received merchandise 'on consignment' form approx. 500 diferent suppliers, often 'ordering' items from them "on the fly". Some (actually most) required that we return (or outright purchase) the inventory within 90 days. Some "YoYo" in a hurry to get merchandise into the warehouse dreamed up the "TBD" supplier, with dummy accout #, address, return policy ... which then became a "legal" supplier. The collective "Nit Wit" in purchasing randomly used "TBD" to order items from any supplier which wasn't already in the system. Obviously, we then were unable to return merchandise to the vendor - simply because we didn't know who it was! By the time we tracked down the problem, then attempted to match individual purchase orders to receiving reports to "real world" vendors, a lot of the return by dates were "past due". The company ended up "owning" a LOT of inventory. Un-necessary. Wasteful. EXPENSIVE! Jobs lost.

The reaction will be "it can't (or won't) happen here because ...

It [red]WILL[/red] happen. Some variation will be in the process. Some 'excuses' will be made. It [red]WILL[/red] happen.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[RickSpr]

There's another downside, too. Chances are, you don't want the TBD supplier to show up in most reports or queries on the data. That means you'll have to remember to exclude "TBD" all over the place, which makes development a little more complicated. Also, "TBD" orders are kind-of-yes, kind-of-no data, something between non-existent and "real". If the boss asks how many outstanding orders there are, you'll have to get him to specify whether that includes some or all of the TBD orders.

When you let vagueness creep into your data, you're ruining your database.

On the subject of referential integrity, there are 2 points of view. The mainframe database administrator, who has to reorg the tables periodically, has a much tougher job if RI is enforced, so they argue against it. But if the data integrity is needed, and you don't use built-in RI, then you have to implement it in program code instead, so the applications people argue to have it in the database. (Plus, if you enforce built-in RI you don't run the risk of erroneous or inconsistent code getting into production. Minor flaws could easily get through testing and not be caught until you hit production volumes, where they can cause hangs.)

In Access, you don't reorg individual tables, you just compact the database, so the DBA's point of view doesn't apply. I always use RI in Access databases, where it's needed. There is a performance hit, yes, but if integrity is needed, you'd get even more of a performance hit from implementing it in code. Rick Sprague