How to explain to users the advantages of dumb primary key?

Posted by Hao on Stack Overflow See other posts from Stack Overflow or by Hao
Published on 2010-04-23T03:36:13Z Indexed on 2010/04/23 3:43 UTC
Read the original article Hit count: 544

Primary key attractiveness

I have a boss(and also users) that wants primary key to be sophisticated/smart/attractive control number(sort of like Social Security number, or credit card number format)

I just padded the primary key(in Views) with zeroes to appease their desire to make the control number sophisticated,smart and attractive. But they wanted it as: first 2 digits as client code, then 4 digits as year year, then last 4 digits as transaction number on that client on a given year, then reset the transaction number of client to 1 when next year flows. Each client's transaction starts with 1. e.g. WM20090001, WM20090002, BB2009001, WM20100001, BB20100001

But as I wanted to make things as simple as possible, I forgo embedding their suggested smartness in primary key, I just keep the primary key auto increments regardless of client and year. But to make it not dull-looking(they really are adamant to make the primary key as smart control number), I made the primary key appears to them smart, on view query, I put the client code and four digit year code on front of the eight-zero padded autoincrement key, i.e. WM200900000001. Sort of slug-like information on autoincremented primary key.

Keeping primary key autoincrement regardless of any other information, we are able keep other potential side effects problem when they edit a record, for example, if they made a mistake of entering the transaction on WM, then they edit the client code to BB, if we use smart primary key, the primary keys of WM customer will have gaps in their control number. Or worse yet, instead of letting the control numbers have gaps/holes, the user will request that subsequent records of that gap should shift up to that gap and have their subsequent primary keys re-adjust(decremented).

  • How do you deal with these user requests(reasonable or otherwise)?
  • Do you yield to their request?
  • Or just continue using dumb primary key and explain them the repercussions of having a very smart/sophisticated primary key and educate them the significant advantages of having a dumb primary key?

P.S.

quotable quote(http://articles.techrepublic.com.com/5100-10878_11-1044961.html):

"If you hold your tongue the first time users ask what is for them a reasonable request, things will work a lot better in the end."

© Stack Overflow or respective owner

Related posts about database-design

Related posts about primary-key