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.