Is it “OK” to have a table that only contain foreign keys

database-design

By "OK", I mean "it doesn't have any bad impact with the overall database design".

  • For example, will it cause insertion problems?
  • Or will it be a problem in the future when I add field in the table?

For example, I have a simple database with three tables:

  • Customer(CustomerID, CustomerName)
  • Product(ProductID, ProductName)
  • Order(OrderID, CustomerID, ProductID)

It is clear that the Order table is only a mapping between the two tables (Customer and Product). The OrderID in table Order has no use, it is there only to identify each row.

How about if I don't want to identify each row because for example we have the rule:

A user can't order the same product twice.

Of course, it is far from reality, but it is only an example.

The bottom line question is, in the example given above, is it OK to remove the OrderID in the table Order and setting both ProductID and CustomerID from table Order as a composite primary key? In this case, the Order table only contains foreign keys and are set as a composite primary key.

As a beginner, I have searched a lot about database design concepts and found some terms like "primary key", "composite primary key", "database normalization", etc., but I don't know what will happen in practice so I decided to ask here. I am just worried about its future implications because I don't know if there is a particular circumstance that it will be a disadvantage.

Best Answer

There is no problem having a table that consists of foreign keys only. In your particular example it would be OK to remove the OrderID. Relational theory states that each table should have a candidate key so, ideally, the business rule you mention would be in place to make the foreign key only table a "good" table. In practice, DBMS software will not care and will allow a table without a unique constraint.

There will not, generally, be any problem with inserts as long as you observe the uniqueness business rules you decide for your system. Conversely, without uniqueness, you may end up with duplicate rows from a SELECT which the application would have to deal with.

If you add further columns in the future there will be no problems due to the existing columns all being foreign keys.

This situation is very common when there is a many-to-many relationship between entity types. This is implemented as an intersection table containing the foreign keys.

Related Question