Originally posted by kgb
I just had questionable problem with another DBA he is going to use varchar2(25) as primary key in OLTP system and as well in DSS system just for more readable logical scheme.
Tell the other dba to get a clue .

I'm sorry, but he wants to use a varchar2(25) field for an index 'so he can have a more readable *logical* schema'!!!?? That is not his primary job!!! There are 2 goals in database design:

- The design must meet the requirements of the application

- The design must meet the requirements of the database

The second point is the key. This means, primarily, flexibility and performance. He is sacrificing performance so that some *logical* model will be more readable? Incredible. A numeric PK is almost *always* preferred. The only default exception, IMHO, is the State_CD (if you're in the U.S.), because of its nature. IMHO, if the string is *very* well defined, nearly static, and very small, then it can be a PK. But even then, I generally only allow this for code tables. Because, if nothing else, most string values in primary tables come from the user, and I am rather opposed to using user-supplied data in a PK - it's too dangerous and introduces too many issues. However, even if the data is *very* clean, I would still fall back to the efficiency issue. Why would you want to use this large string as a PK? It would make your PK index larger. This field would also then be propogated to all the related tables. This would make those tables larger. This would make the indexes on those FKs larger as well. Not only are the indexes larger, and therefore inherently less efficient, but string comparisons are also inherently less efficient than numeric comparisons.

Actually, this makes me think of a good example I need to build. I need to take the demo model I have been building and use ERwin's size-estimation feature/add-on. I am willing to bet that IF I were to use the user-supplied AK fields from all the tables as PKs, my total database size would grow by at least 20%. That, together with maintaining multi-part keys - another pet peeve of mine I personally advocate abstracted keys as the default methodology. Anything else has to have a solid argument behind it. But I need to build that model in both fashions and then not only size them but run some timing tests on different queries...

Hmmmm...

Thanks for the idea .

- Chris