RI vs no RI

[rickj65]

I'm creating a SQL database for a client and in a recent implementation discussion, was instructed not to use RI. This confused me as I've never developed a normalized relational database without implementing some kind of RI - whether it be through DRI or triggers. It was explained to me that RI consumes too many resources on the server and I should enforce cascade updates and deletes at the application level.

Can anyone briefly enlighten me on this subject and whether the argument has merit? In particular, are the performance trade-offs in implementing RI vs. not implementing it that significant?

The db I'm developing is largely used for analytical as opposed to transactional processing. It is primarily a monthly aggregation of disparate data sources into one normalized db used for reporting. There are very few row-level updates.

Thanks,
Rick

 

[SemperFiDownUnda]

OMG! Who says you should do that?

RI is a data centric task. The further away from the database you perform the task the more resources you are going to use AND the greater chance you will miss something.

That being said....if your server is to be a OLAP server then its source could be the one that maintains the RI. But then if you've got a OLAP server you should have it built from the source databases and I'd question why an application is maintaining data.

Using DRI is more efficient then triggers because DRI effects records before they hit the transaction log where Triggers occur after it hits the transaction log therefore they need to rollback the changes.

So in the end on your source database use DRI and or Triggers. On your OLAP server it is built from the source so it should be ok.