Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SunnyDee
Yak Posting Veteran
79 Posts |
 Posted - 2011-01-01 : 20:20:42
|
| Hi All:I'm working on a project with an inherited database architecture that uses an identity column as the primary key. The foreign keys link to the primary key (identity field) and there is no other unique constraint in the design. One of the problems that arises is because there is a business need to have more than one instance of the database across servers. They do not use SQL replication. Some of the data is considered 'configuration', and there is also a business need for the 'configuration' based data to be synched.The problem is further compounded because they use backup and restore as a method of managing the data among several instances. They request that users double the data entry to allow for the restore. It seems to me that using an identity column as a foreign key is a poor database design, but I cannot find any articles or posts that make my case.My questions are:1) What is the recommended practice for using identity columns as the only key in a table?2) What is the recommended practice for using identity columns as foreign keys?3) What is the best way to overcome a design that does not suit the business needs?Any suggestions/comments are welcome. Thank you for your time... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-01-01 : 20:36:07
|
| It's an interesting topic, and there's quite a bit of debate on this subject. There are many people in the camp who use identity columns as the primary key for EVERY table. There are some that use it almost everywhere. There are some that use the natural key unless one doesn't exist. There are some that use GUIDs for everything (not a good idea for performance reasons). And there are some that are dead set against identities.So it's not like there's a recommended practice for this as there are too many differing opinions. Take for example Kimberly Tripp and Joe Celko. Joe hates identities. Kimberly likes them. Those are two experts in the field, and they have different opinions on the subject. We'll likely get some information from Joe on this very topic as he watches for posts like this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-02 : 04:45:23
|
| The IDENTITY is not the source of you problem, surely?It is that the business wants some data in two places.Even if you had a Natural key you would get collisions. EVEN if you use a GUID you MIGHT get collisions (it is not guaranteed unique, although it would be very bad luck to get a collision, and maybe "sequential GUIDs" are based on the MAC address of the network card - which would improve things.If you need data in two places how about triggers on the appropriate tables that "also" save the change to the "other" database?You can copy "just changes" from one database to another (DELETE if no longer in Source Table, UPDATE if in both tables AND there is a different in one/many columns and finally INSERT if ONLY in the Source Table). However, that will not help with "collisions" so you need a strategy for collision detection and how to resolve them.You could have a SOURCE column that indicated WHERE the record was last updated. Thus if there is a difference in a row but the SOURCE has not changed, then it has been re-modified on the same database, and can safely be copied across.If the SOURCE is different then there is a collision. Maybe some columns can be resolved automatically, but others may require a human to decide (an address, for example. If there are two updates maybe one was fixing a typo and the other was a change-of-address).The more often you SYNCHRONISE the two databases the fewer the number of collisions. If you do it in real time there will be no collisions, if you do it once a month there may be a lot! once a day fewer ... once an hour fewer still ... |
|
|
|
|
|
|
|
|
Using identity column as the only key