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.
Let's suppose I have an
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
- 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.
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.
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.
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.)