Composite primary keys: good or bad

Question

I am developing a database for an online store system. The question that I came across after reading some posts on this website is that although I can use composite primary keys in the case, which I will discuss below, is it really bad practice (according to the messages I read in this regard, regarding the stack, many say that this is bad practice, so I ask).

I want to store payments for orders in a separate table. The reason is that there can be many elements in an order that are processed in a separate table in the form of relationships from many to many. Now, if I do not use composite primary keys for my payment table, I will lose my unique PaymentID :

 [PaymentId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [OrderId] INT NOT NULL PRIMARY KEY --Also a Foreign Key-- 

Now, if I just delete the main key for OrderId , I will lose one-to-one relationship here, so Many OrderIds can be associated to many PaymentIds , and I don't want that.

This is why the questions previously asked here concluded (basically) that a composite key is a bad idea. Therefore, I want to clarify this for myself; if this is bad, then what is the best practice?

Answer 1

There is no conclusion that composite primary keys are bad.

Best practice is to have some columns or columns that uniquely identify a row. But in some tables, a single column alone is not enough to uniquely identify a row.

SQL (and the relational model) allows you to use a composite primary key. In some cases, this is a good practice. Or another way to look at it is not a bad practice in all cases.

Some people believe that each table should have an integer column that automatically generates unique values ​​and should serve as a primary key. Some people also claim that this primary key column should always be called id . But these are conventions, not necessarily best practices. Conventions have certain benefits, as they simplify certain decisions. But conventions are also restrictive.

You may have an order with several payments, because some people buy on layaway , otherwise they have several payment sources (for example, two credit cards) or two different people want to pay for a share of the order (I often go to a restaurant with a friend, and we each pay for our own food, so staff processes half the order on each of our credit cards).

I would develop a system that you describe as follows:

 Products : product_id (PK) Orders : order_id (PK) LineItems : product_id is (FK) to Products order_id is (FK) to Orders (product_id, order_id) is (PK) Payments : order_id (FK) payment_id - ordinal for each order_id (order_id, payment_id) is (PK) 

It is also related to the concept of identifying relationships . If he determines that the payment exists only because there is order, then make part of the primary key order.

Note that the LineItems table also lacks its own auto-increment, a primary key with one column. The many-to-many table is a classic example of the good use of a composite primary key.

Answer 2

This question is dangerously close to asking for opinions that may give rise to religious wars. As a person who is very biased that my tables automatically increment entire primary keys (which is called TablenameId , not `Id), there is one situation where it is optional.

I think the other answers are about why you need primary keys.

One very important reason for reference purposes. In a relational database, any object can - in theory - refer to another object through foreign key relationships. For foreign keys, you definitely want a single column to uniquely identify a row. Otherwise, you will have to deal with multiple columns in different tables that align with each other. It is possible, but cumbersome.

The table you are referring to is not an entity table, it is a join table. This is a many-to-many relational database. Since it is not an entity, it should not have a relationship with a foreign key. Therefore, a composite primary key is reasonable. There are some situations, for example, when you are concerned about the size of the database, where it is desirable to leave an artificial primary key.

Answer 3

Best practice is always the best for your application. Do not look at arbitrary “best approaches”, otherwise you may still find yourself in a worse application.

A component key in SQL makes a combination of the included keys be unique. Most likely, there will be an exponential increase in complexity for each included key, which can mean exponential degradation of performance depending on how good the performance optimization that database engineers were able to do. As the table grows, it will also deteriorate more quickly with the help of compound keys.

However, an exponential decline in performance can be a good deal. Until your table contains millions of rows, the convenience of a key combination, always unique, can be justified. Also, if you have tables with millions of rows, you are likely to have problems worth thinking about, whether your keys are composite or not.

As with all technical solutions, its balance is in effect. Is a decrease in productivity beneficial?

Another solution that is very common is to use a so-called surrogate key, and then apply a unique index constraint to keys that must be unique in combination. This makes table lookups less complex since it includes only a regular index.

Answer 4

Disk space is cheap, so a primary key, grouped by identifier int (1,1), named after the agreement (for example, the name of the table is pk +), is good practice. This will simplify the management of queries, joins, indexes, and other constraints.

However, there is one good reason: not necessary (in MS SQL Server): if you want to control the physical sorting of your data in the underlying storage system.

The primary clustered key determines the physical sort order. If you do this in the identification column, the physical sort order will be basically the insertion order. However, this may not be the best, especially if you always query the table the same way. On very large tables, getting the correct physical sort order makes queries much faster. For example, you might need a clustered index of two columns.

Answer 5

Payment can be considered as an order, so I tend to have only two tables, one table for orders (billing, delivery details, payment identifier) ​​and one for items in the order (product name, quantity, price).

The order ID can be the primary Primary key, and you can only have a unique constraint on the payment ID. Or, if the order identifier and payment identifier are the identifier as a whole, you can create an identifier field that automatically expands.