Picking the best primary key + numbering system

Question

We are trying to come up with a numbering system for the asset system that we are creating, there has been a few heated discussions on this topic in the office so I decided to ask the experts of SO.

Considering the database design below what would be the better option.

Example 1: Using auto surrogate keys.

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 1                   1

Example 2: Using program generated PK

=================   ==================
Road_Number(PK)     Segment_Number(PK)
=================   ==================
 "RD00000001WCK"     "00000001.1"

(the 00000001.1 means it's the first segment of the road. This increases everytime you add a new segment e.g. 00000001.2)

Example 3: Using a bit of both(adding a new column)

=======================    ==========================
ID(PK) Road_Number(UK)     ID(PK)  Segment_Number(UK)
=======================    ==========================
 1     "RD00000001WCK"       1       "00000001.1"

Just a bit of background information, we will be using the Road Number and Segment Number in reports and other documents, so they have to be unique.

I have always liked keeping things simple so I prefer example 1, but I have been reading that you should not expose your primary keys in reports/documents. So now I'm thinking more along the lines of example 3.

I am also leaning towards example 3 because if we decide to change how our asset numbering is generated it won't have to do cascade updates on a primary key.

What do you think we should do?

Thanks.

EDIT: Thanks everyone for the great answers, has help me a lot.

Answer 1

This is really a discussion about surrogate (also called technical or synthetic) vs natural primary keys, a subject that has been extensively covered. I covered this in Database Development Mistakes Made by AppDevelopers.

Natural keys are keys based on externally meaningful data that is (ostensibly) unique. Common examples are product codes, two-letter state codes (US), social security numbers and so on. Surrogate or technical primary keys are those that have absolutely no meaning outside the system. They are invented purely for identifying the entity and are typically auto-incrementing fields (SQL Server, MySQL, others) or sequences (most notably Oracle).

In my opinion you should always use surrogate keys. This issue has come up in these questions:

• How do you like your primary keys?
• What’s the best practice for Primary Keys in tables?
• Which format of primary key would you use in this situation.
• Surrogate Vs. Natural/Business Keys
• Should I have a dedicated primary key field?

Auto number fields are the way to go. If your keys have meaning outside your database (like asset numbers) those will quite possibly change and changing keys is problematic. Just use indexes for those things into the relevant tables.

Answer 2

I would personally say keep it simple and stay with an autoincremented primary key. If you need something more "Readable" in terms of display in the program, then possibly one of your other ideas, but I think that is just adding unneeded complexity to the primary key field.

Answer 3

I'm also very strongly in the "don't use primary keys as meaningful data" camp. Every time I have contravened that policy it has ended in tears. Sooner or later the meaningful data needs to change and if that means you have to change a primary key it can get painful. The primary key will probably be used in foreign key constraints and you can spend ages trying to sort it all out just to make a simple data change.

I always use GUIDs/UUIDs for my primary keys in every table I ever create but that's just personal preference serials or such are also good.

Answer 4

Don't put meaning into your PK fields unless...

• It is 100% completely impossible that the value will never change and that

• No two people would ever reasonably
  argue about which value should be
  used for a particular row.

Go with option one and format the value in the app to look like option two or three when it is displayed.