How to explain to users the advantages of dumb primary key?_问答_开发者

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 ye开发者_开发问答ar 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 numbers. Or worse yet, instead of letting the control numbers have gaps/holes, the users will request that subsequent records of those gaps should shift up to those gaps and have the subsequent records' primary keys re-adjusted(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( https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/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."

Is there some concatenation of keys that make a natural synthetic unique key? I suppose not or you'd not be asking the question.

In the same way that your user would not want to know the cylinder/block/head that the record of interest is stored on, they need not know the dumb primary key; it is an implementation detail. There are good reasons for a dumb primary key but they are not business reasons. Hide the implementation detail of dumb keys behind a facade that makes sense at the business level.

Explaining that they are bikeshedding will probably not work to your advantage. Address the expressed need of the customer, that's your job.

I approve of your slight-of-hand. You have to meet the felt need. When possible, I explain that mental retention or understanding of data records is a pre-computer need and people should trust the machine and the system ... well, not worded precisely like that, but you get the idea. But often enough I just nod my head and give them what they thought they needed -- but not as the table key, as they imagine. ~~but at a query level.~~

In fact, my best database work ever -- my current job -- came to me basically because the guy before me didn't get this. He would argue endlessly with the managers for dumb numbers, and adamantly refused to provide anything else. All I had to do was promise "not to be that way."

IMO, the end users do not need to know the advantages/disadvantages of different types of primary keys.

The exact design and implementation of their data within the database should be a seen as a black box to end users. The bottom line being that the output of this black box is the data in the correct format that they require : users shouldn't need or have to know whether particular field(s) in question are actually the PK or generated from it.

According to Einstein, everything should be made as simple as possible, but not simpler than that. Simplicity is sometimes in the eye of the beholder. If the users think that smart keys are simpler than dumb keys, you will do well to accomodate them, in the views.

By continuing to use a dumb key in the base table, you avoid several of the pitfalls that eventually come about when smart keys are used.

Knowledge is power. So is data. When data gets shared, power gets shared. When power gets shared, politics happens. Diplomacy is part of the job.