SQL Server Performance Forum – Threads Archive

Database Design

Is it considered poor database design if you don’t define relationships in your database? What are the pros/cons? Is there an article here that explains how FK’s work, like cascade update related fields and cascade delete related fields, etc.?

http://www.sql-server-performance.com/database_design.asp
http://www.sqlteam.com/item.asp?ItemID=122
http://www.devasp.net/net/search/display/category629.html
http://databases.about.com/od/specificproducts/
http://vyaskn.tripod.com/coding_conventions.htm HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Relationships help document your database, and the corresponding constraints help maintain integrity. I use constraints, and try to use relationships where possible but sometimes for complex schema they can become a major hindrance. There are arguments for and against for using cascading updates and deletes. Personally I really dislike them and never use them, preferring instead to do all my logic explictly inside a stored proc

Thanks for the replies! Another question I have is on moving data from one environment to another. I use the identity property to generate my PK’s. If I have two separate hosted environments, what’s the best tool to move a customer’s data from one database to another? The PK’s will probably conflict, therefor will need to be reassigned. It seems like defining relationships in SQL would help in that process… Or else, I may just have to write a program to do it.

I would recommend exporting the data from the source and inserting into the new database just as if new records were being added to the destination. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Maybe I didn’t explain it well enough. Let’s say you have 2 tables; customers and orders. You also have 2 databases, each with it’s own data (separately running applications). Now you want to move some of the customers from one database to the other. The Primary Keys will have to be recreated in the destination database, as they were possibly already used for other records. Additionally, the newly created PK’s will have to be used in the orders table in place of the customers old Primary Key (to link the customer with his/her orders). Some sort of logic would have to be used to ensure that the relational data gets updated with the new Primary Key. What I’m asking is… what is the best way to do this? Is there a recommended tool, etc.?

Maybe your response did answer my question, but I’m trying to understand how the relational data would get updated…

In your case you will probably need to set the CASCADE ON UPDATE option on to update all child records (FKs)with Parent record (PKs). So when you update all the primary keys the foreign keys will also be updated. Remember to be very cautious when using these settings also perform backups before you attempt any of this. Read the links like Satya suggested.

Or in advance set up the first system to use even identity numbers 2,4,6,8 and the other system to use odd numbers 1,3,5,7. Then you won’t have any issues.

]]>