0

Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it will be easier to read in the other table and it reduces the chance to have duplicated items. But this is my opinion, I don't really know if there is a standard or if one approach is more efficient than the other.

8
  • 3
    The name might change. That makes for a bad PK. – usr Jun 26 '15 at 22:18
  • 1
    @usr I disagree. It may be bad as a clustered index - in some cases and implementations. That it is always bad is a generalization that is not obviously true. – ypercubeᵀᴹ Jun 26 '15 at 22:32
  • 3
    @ypercube : Not just for clustered index. Updating PK column value can trigger bunch of cascade updates in detail tables , which in turn means more locks and potentially deadlocks. – a1ex07 Jun 26 '15 at 23:33
  • 1
    @a1ex07 You are missing my point. I did say that is some (many if you wish) implementations and cases, it can be bad. Do you argue there is no case it can be ok or good? – ypercubeᵀᴹ Jun 26 '15 at 23:48
  • 2
    I would suggest that using a "name" (any human familiar string) for a primary key is really only safe if the set of values is controlled by an outside standards body that will naturally manage change to be infrequent, such as ISO country or currency codes. In other cases changes and even key conflicts are likely. I know there are three different Joel Browns in my neighbourhood, for example. – Joel Brown Jun 27 '15 at 12:27
6

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's.

A key ideally has a number of properties

  • unique
  • stable -- should not change frequently, not necessarily immutable though
  • irreducible -- no subset of the attributes qualify as a key
  • simple -- few attributes
  • familiar -- meaningful to the user

Quite often there is a conflict between these properties and some kind of compromise has to be made. A natural key - such as name in your case - may not be stable enough and therefor is a bad choice for a key. A surrogate key on the other hand is not familiar to the user.

To conclude, there is no easy answer to your question, it depends on the situation. Design your keys, and make the best out of it. I would not recommend blindly choosing one approach over the other.

2

I'd use a surrogate product id.

This avoids the need to store potentially lengthy strings multiple times across every order they are used in.

Additionally if the product name is ever updated you only need to do this in one place.

It is also more flexible as it allows you to have multiple different products (perhaps over time) with the same product name.

It is trivial to join from Products to Orders to retrieve the name in the times that you do need it. You could even create a view that encapsulates this.

3
  • I agree with the part of the update, didn't think of this at all. But this flexibility you mention I don't think it would be good. Imagine a product named "Bread". Using the Name as PK the next one would be "Whole Grain Bread" or something like that while with the number could be just "Bread" again and the difference would not be visible. Again, it is my opinion – Enthusiast Jun 26 '15 at 18:05
  • 3
    @Enthusiast - You could still optionally enforce the uniqueness with a unique constraint if you wanted. My point is that it gives you more flexibility. Are you going to tell the business that they can't name a product "bread" because that was the name of a deactivated product 5 years ago? – Martin Smith Jun 26 '15 at 18:07
  • 1
    Thinking a little more, people could just, and probably would, name it something like "Bread1" "Bread " "Bread!" just to make it easier for them. – Enthusiast Jun 26 '15 at 18:13
2

Joe Celko thinks very strongly that you should use the "real world" data such as a Name rather than using an artificial value such as an ID. However, many people here on DBA.SE prefer to use an ID value as the Primary Key.

But it is a to some degree a philosophical question, or personal opinion.

An aside: You did not say which brand of SQL (MS SQL Server, Oracle Server, MySQL, postgresql, etc) you are using. If you are using MS SQL Server you need to distinguish between the Primary Key and a Cluster Unique Index. Often the Primary Key and the Cluster are identical, but they do not need to be.

From here: https://stackoverflow.com/questions/15051869/relationship-of-primary-key-and-clustered-index

The answerer said in part: "A primary key is a logical concept - it's the unique identifier for a row in a table. ... A clustered index is a physical concept - it's an index that affects the order in which records are stored on disk."

In SQL Server the Clustered Index value, which could be an IDENTITY column with an INT or a BIGINT datatype. The Cluster Key is what is exists in all the non-clustered indexes that refer to the cluster. So, you do same some space and benefit from performance by using a smaller Cluster Key.

So I prefer using an IDENTITY and an INTERGER value, since it simplifies so much. (One major benefit is that it avoids cascading changes through the database when some entry must change its name.)

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