|
View previous topic :: View next topic
|
| Author |
Message |
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Hi,
I have to introduced new tables which are child tables of a parent table.I am a bit confussed whether to use referential integrity at the application level or define it in the database.
The pros and cons of enforcing the Referential integrity in the database is as below :
Advantages :
- If we handle it at the business level then possible error might happen as compared to defining it at the database.
Disadvantages :
- scalability and performance issues
- Only my application will insert the data into the new tables thereby there is NO risk of corrupt data being inserted by some other application if my logic is correctly written.
Also, I don't see any existing tables which are using referential integrity in the database in my apllication thereby a bit reserve to use it for the new development.
Can anyone please suggest whether should i define RI in the database and what might be the consequencesof doing so.
It's an online application and data would be inserted in the table 24 X 7.
Please advice.
Thanks in Advance! |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
| referential integrity at the application level |
Mean what??
| Quote: |
Advantages :
- If we handle it at the business level then possible error might happen as compared to defining it at the database.
|
Mean what??
and Why do you need RI for your new application or whatever, when RI is not being used in your system??? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
much depends on your version of db2,
the size of the tables,
amount of activity and
how much iron you have.
I would not rely on
| Quote: |
| Only my application will insert the data into the new tables thereby there is NO risk of corrupt data being inserted by some other application if my logic is correctly written. |
back in the days of db2 vsn 2 thru 6, all the disadvantages of RI are true. vsn7 was a great leap forward, 8 addressed the db2 environment problems, 9 and 10 are improvements.
I have worked on systems that had application RI written in,
(did not use db2 RI)
and my experience was,
everytime someone made a change, they screwed-it-up.
most shops do not have the talent to support application RI.
my advice is if you are not using a discontinued version of db2
and you have some iron,
use DB2 RI.
That way you only have to code to respond to sqlcodes,
whereas if you use application ri, your are at the mercy of the people who code and modify your system.
I would also ignore the post prior to mine. |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Thanks Dick!
I would also like to check with you whether there would be any difference in the application code(Housekeeping of child table) if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it. I believe there won't be any difference in the application code to delete the record from the child table.i.e. I would have to ensure that all the records from the child table are deleted first before the record from the parent table is deleted in both the cases. Defining RI at the database level would just work as an additional check.
Is there anything specific I need to take care of while defining RI at the database level.
Many Thanks ! |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
well, i was composing a response in my mind as i read your post,
but when you said
| Quote: |
| while defining RI at the database level. |
I quashed that quickly and suggest that you do some reading.
RI is defined at the table(s) level.
i have no idea what version of db2 you have, or what kind of box you are running on.
and, no, I don't need an answer.
I suggest that you wait for GuyC or one of the other db2 types to answer your questions.
Also, I would check with your dba's, as they are going to be the ones to maintain the system. |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Hi Dick,
Apologies I meant at the table level only.
" if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it".
The records will be inserted in the child table online transaction and would be deleted by a batch module.
Please suggest if there's anything specific I need to consider while defining RI at the table level. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| With database RI, your child can get in Check Pending (Load,...) |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rahul,
| Quote: |
" if I have enforced RI(DELETE RESTRICT) in the DB2 child table or without using it".
|
If you have specified DELETE RESTRICT, you can delete rows from the child table. When deleting from parent table, if child rows exist, error occurs and no rows are deleted.
Advantages
When RI is maintained by DB2, it will make sure integrity is maintained during INSERT, UPDATE, DELETE, LOAD or RECOVERY.
You code can more focus on business logics.
Disadvantages
DB2 has to do additional check when a DML activity or LOAD is performed. Tiny stress there.
Additonally CHECK DATA utility needs to be run to verify the integrity when DB2 suspects referential problem.
Just googled there seems to be a REDbook about it. Check it out
Usually i have seen for online tables having DELETE CASCADE(caution) / DELETE RESTRICT and same warehousing tables with no RI. It differs shop-to-shop based on their experience and trial-and-incident method best practices are made.
Thanks,
Sushanth |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Many Thanks for the valuable information!
In my application ,the tables will be inserted,Updated through online transactions (parent and child both). The tables will be deleted through batch module.
RI is defined in the child table DELETE RESTRICT. I would like to know whether there would be any perfomance issues while inserting or updating the parent/child table. As this would be done online thereby I am a bit worried about the performance.
The tables won't be uploaded through LOAD utility. Only in case of any recovery, the tables would be loaded and the necessary action such as to bring the table in Read/Write status from load pending can be taken care of.
Thanks! |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Rahul,
How many rows will be inserted into parent and child table via online application in a hour or day ?
For one parent row, how many child rows will be available ?
Is it just one-parent & one-child hierarchy or is there a whole big level of hierarchy ?
| Quote: |
RI is defined in the child table DELETE RESTRICT. I would like to know whether there would be any perfomance issues while inserting or updating the parent/child table. As this would be done online thereby I am a bit worried about the performance.
|
Make sure indexes are available for the foreign key and columns in the indexes are in right order and organized, this should take care of most of the things.
Catch with DELETE RESTRICT is, for every deletes in the child table, it has to access the parent table for checking purposes whereas cascade is one-shot, delete the parent row and child-rows are gone, the number of calls between parent and child tables are less. Cascade can have negative effects when there is a bigger heirarchy or millions of rows in child table. Also, setting the RI as cascade will invalidate the packages.
To evaluate and choose the best RI policy, basis on integrity and performance, you have to Know Your data(KYD). When RI is involved there is always room for maintainence.
Check out this article also
Thanks,
Sushanth |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Hi Sushant,
Plaese find my replies for your queries :
How many rows will be inserted into parent and child table via online application in a hour or day ?
Parent = 100 records per day
Child = 5000 records per day
For one parent row, how many child rows will be available ?
one parent row can have around 20 child rows
Is it just one-parent & one-child hierarchy or is there a whole big level of hierarchy ?
its a 3 level hierarchy parent -->child1 --> child2 ---> child 3.1
--->child 3.2
Child1 is the parent of child2 and child 2 is the parent of child 3.1 and child 3.2
In case of a insert to the child table online , it will check the parent table for the corresponding key. Will it degrade the performance as compared to if the tables were independent without table level RI.
In case if a record is to be inserted online in child 3.1 then will it check for the complete hierarchy and will it de-grade the performance of the online system.
I think deletion is not a contraint as it will be done through batch and not online but still I think the performance in case of deletion will also degrade. pls suggest. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rahul,
| Quote: |
In case of a insert to the child table online , it will check the parent table for the corresponding key. Will it degrade the performance as compared to if the tables were independent without table level RI.
|
With the row counts you have given, i don't think there gonna be any problem.
| Quote: |
In case if a record is to be inserted online in child 3.1 then will it check for the complete hierarchy and will it de-grade the performance of the online system.
|
I don't think so, it checks the parent table(2) when you insert a row to the child table(3.1), since there are no updates done in table(2), it shouldn't check its parent.
Just
| Quote: |
Make sure indexes are available for the foreign key and columns in the indexes are in right order and organized, this should take care of most of the things.
|
Thanks,
Sushanth |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Thanks Sushanth,
I think it will be fine to enforce RI in the tables then but will just have to ensure that proper sequence is maintained while inserting/deleting the records.
Thanks all for your help !! |
|
| Back to top |
|
|
|
|
