Picking the best primary key + numbering system

Asked 2009-04-01T22:55:08.063

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.

alt text

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

database database-design primary-key

Edit Report

3 Answers

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.

answered 2009-04-01T22:57:10.867

As mentioned before, keep your internal primary keys as just keys, whatever the most optimal datatype is on your platform.

However you do need to let the numbering system argument be fought out, as this is actually a business requirement, and perhaps let's call it an identification system for the asset.

If there is only going to be one identifier, then add it as a column to the main table. If there are likely to be many identification systems (and assets usually have many), you'll need two more tables

    Identifier-type table             Identifier-cross-ref table
      type-id             ------------> type-id              (unique
      type-name                         identifier-string     key)
                                        internal-id

That way different people who need to access the asset can identify in their own way. For example the server team will identify a server differently from the network team and different again from project management, accounts, etc.

Plus, you get to go to all the meetings where everyone argues with each other.

answered 2009-04-05T10:50:33.083

I would go with the surrogate key, but you may want to have a computed column that "formats" the surrogate key into a more "readable" value if that improves your reporting. The computed colum could produce example 2 from the surrogate key for instance for display purposes.

I think the surrogate key route is the way to go and the only exceptions that I make for it are join tables, where the primary key could be composed of the foreign key references. Even in these cases I'm finding that having a surrogate primary key is more useful than not.

answered 2009-04-10T13:42:26.457

Picking the best primary key + numbering system

3 Answers

Your Answer