2

We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to auto increment a unique id (int) for each new customer.

Currently all customers we add do have a personal number but are there any good examples of why this may be a good or bad implementation?

One argument is that using an incremental id would simply add an extra field which is unnecessary and take up more space.

The personal number is stored as a varchar and the customer table holds close to a million customers. The personal number or id would be added as a foreign key for orders and customer support requests.

How do I know whether these are good or bad implementations? How would I argue either side?

  • 1
    Who is giving this 'personal number'? What is the size of this 'personal number'? – Marco Nov 4 '15 at 15:21
  • Does the personal number have to be a varchar or can it be stored as an integer? Also is it always going to be unique even if staff members leave? – James Anderson Nov 4 '15 at 15:24
  • 1
    @Marco it would come from another system that handles sales, it would be verified and matched to a name and address before being added. It consists of 10 numbers and a dash. – Matkey Nov 4 '15 at 15:25
  • 1
    @JamesAnderson Currently it is stored as a varchar but I guess it could be turned into an int by remvoing the dash mentioned in my earlier comment. – Matkey Nov 4 '15 at 15:27
  • Even though you have tagged your question as mysql you may find my question and the associated answers interesting regarding performance of natural vs surrogate keys. – Max Vernon Nov 4 '15 at 15:33
3

Some good criteria for choosing or designing keys are: Simplicity, Stability and Familiarity but first you need to understand the fundamental business requirement your key is supposed to address. Ask yourself how you intend to identify customers in your business processes. Assuming you have a requirement to identify customers and Customer Number is the means your business will use to do that then it makes perfect sense as a key in your customer table - not necessarily the only key, but certainly a key of that table.

2

What we do in the BI world is keep all Natural Keys from source systems while concurrently using a DB generated ID (Surrogate Key). Your person number would be the NK in this terminology. Keep it to allow trace-ability and change tracking when, or if, this number ever changes. You retain all historic/source system IDs, while enabling a new and more efficient/controlled PK in your solution.

Use the incrementing integer for performance gains in joins, simplicity of querying, and DB consistency. When you control the ID you can guarantee uniqueness and when/how to assign this. With an externally provided ID you have no control over potential changes or clashes.

I wouldn't think you could have a space concern. Even a rough estimate of the space needed to store 1 million 8 byte integers in a standard disk size, say terabytes, is inconsequential.

  • 2
    You may have a space concern, not so much on-disk as in-memory. If you have 80 tables with that 12-byte varchar, where you could use a single int, you'd save quite a lot in both processing time, and memory. – Max Vernon Nov 4 '15 at 19:52
1

What you are describing is generally bad practice. You mention dashes in the number but don't mention what the number is. I'm guessing that it's something along the lines of a social security number or a phone number.

If it's a social security number then there may be privacy concerns with using this number. You won't be able to mask the number for security purposes without destroying any foreign key constraints you have in place.

If it's a phone number, what is going to be done when someone's phone number changes or they have multiple phone numbers? (I.e., they gave you their work number but now want to use their cell).

Both of these also have concerns as the number can be determined through external databases (phone books, tax records, etc.).

I would suggest that you use an auto-incrementing ID for the primary key of these entities. If you use this as the key for the clustered index then that will have the added benefit of low fragmentation as new entities are added at the end of the table rather than needing to be inserted at random.

  • 1
    I agree. A good primay key is meaningless and immutable, in addition to uniquely identying a person, place, or thing. I also agree with using an auto incrementing number. One caveat: the responibily for prevetin the same person from being included twice rests with the programmers or the users. – Walter Mitty Nov 4 '15 at 17:06
  • Data cleanliness has always been the responsibility of the end users and the programmers. Nothing to stop them from including the personal number (hashed or whatever) and easily checking for duplicates that way. Maybe a function that when it's saving a new user, that it does a check to see if it might be a duplicate and ask the user to verify. – Jonathan Fite Nov 4 '15 at 17:25
  • 2
    Identifying customers with a customer number is not "generally" bad practice! It's pretty much essential practice in many scenarios. You are just guessing that the number in question is a social security number or a phone number and on that basis you are claiming it is "bad". – nvogel Nov 4 '15 at 18:58
  • I agree that it has always been the responsibility of end users and programmers. However, many DBAs are familiar with referential integrity constraints, and figure, correctly that the DBMS can protect itself from orphaned references. They reason, by extension and incorrectly, that the primary key constraint protects against entity duplication, because it protects against key data duplication. More often, they just don't think of this, and they need to. – Walter Mitty Nov 4 '15 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.