2

In "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.

Off 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 composite primary key? In this case, the Order table only contains foreign key and is set as 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.

2

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.

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.