Brian Bontrager wrote:Thanks!
Welcome to the forum!
It depends somewhat on your data model, with a dash of personal preference. If your natural key will not change and is fairly compact, it can be every bit as good as a derived primary key (such as a number from a sequence).Well, maybe I can share more of what I am working on? (BTW, I have lots of other questions, but I'm trying to ask for help with little "bits" of the bigger problem.)
In some cases performance can be improved by using the natural key as the primary. You would not need an "extra" index on the natural key in that case... fewer indexes can help insert/update performance.
Iordan Iotzov wrote:Thank you!
Welcome to the forums!
The answer, as usual, is “it depends”.I figured!
I think there are two major aspects of the surrogate key/natural key debate – “flexibility” and performance.Well, not to get too far ahead, but let me share even more than what I just did responding to Brian...
A “flexible” database design is one that can relatively easily accommodate changes and new requirements. Since changing a primary key is typically a difficult thing to do, using surrogate keys for a primary key give “flexibility” to a system. The impact of a natural key change on the DB design would be significantly smaller is surrogate keys are used.
Generally speaking, using surrogate keys would require more disk space and it would take some, usually small, toll on performance. The performance impact can vary though. If the tables are implemented as Oracle Index-Organized tables or as MySQL tables (not 100% sure for that), the primary key is stored in all indexes. If the surrogate key is smaller than the natural one, then the surrogate key can reduce storage footprint and even improve performance for some queries.
991215 wrote:You are walking a very fine line here. A lot of us have blogs that we use as a quick reference for what are really FAQ questions. When I link to mine, it is readily apparent that I am referencing my own work, and it is strictly non-commercial. In your case, the site is festooned with commercial content, and your posting history is devoid of any real content beyond the link to your decidedly commercial blog. Not to mention that some of your content looks suspiciously like MOS content, which, if true, puts you in violation of your support agreement.
Could you please redefine your problem ?
1004338 wrote:There is, but unlike some other fora, there is not an icon to click on to get it. The trick is what we refer to as 'code tags' They look like this: \
P.S. BTW, isn't there anything here in the forums to preserve FORMATTING and TABS?? :(
sql> select sysdate from dual;
Put one on the line just before the text you want to preserve the formatting, and one just after. As you are creating your message, it will look like this: \
sql> select sysdate from dual;
And when you post it it will look like this:
You can use the "Preview" tab to see what a posting will look like before you pull the trigger.
1004338 wrote:As others have stated, there are two schools of thought and a healthy does of 'it depends'. I've come to a more doctrinaire position in favor of synthetic PK, derived from a sequence. You'd be surprised at the natural data elements that appear to be "unchangeable" -- that are changeable. Even a Social Security Number. There are some elements of data that truly are cast in steel (literally -- like an automobile's VIN), but they are few and far between.
Hi there. First time poster, and fairly new to the database world.
Up front, I would like to say that I am working with MySQL, but since I consider Oracle people to be some of the smartest, I figured I'd join and try to learn from the pros!!
I would like to know if it is "bad" to use Natural/Physical Keys as the Primary Key in a table?
How does this affect performance?
I have some tables which are basically "look-up" tables, and it would be much easier for me to work with Natural Keys, but I don't want to choose something that is easier for me, that would mess up my database.
As others have stated, there are two schools of thought and a healthy does of 'it depends'. I've come to a more doctrinaire position in favor of synthetic PK, derived from a sequence. You'd be surprised at the natural data elements that appear to be "unchangeable" -- that are changeable. Even a Social Security Number. There are some elements of data that truly are cast in steel (literally -- like an automobile's VIN), but they are few and far between.But of course, google "VIN cloning." People trust the bar code on the dash without looking at the stampings. And this is enlightening: http://www.hemmings.com/mus/stories/2006/07/01/hmn_feature16.html?t=printable Often with motor vehicle departments, all they care about is the column being filled, they just have you sign a statement and buy a bond to cover their butts for anything weird, like the common use of frame pieces to register hot rods.
I think if I had a single-column look-up table -- just providing a somewhat dynamic list of valid values -- I might consider foregoing a separate PK column. But others may point up a fallacy there as well.Probably the determining factor is what else has been done in an existing system.