Avoiding composite keys - help

[DevInCode]

Hi there. All I read about composite keys is that they should be avoided. Here is my basic problem:

Very Basically the three tables would be Orders, Order_items, and products
Orders would have an autonumber PK (oID)
Products would have an autonumber PK (pID)
Order-items would need a composite PK to be unique. It would consist of the oID and the pID.

I understand that isn't the preferred method of doing things. This is just a small project and it won't be complicated but for the sake of experience I would like to follow best practices. How can I work around this?

[Stewart Ross]

Hi, and Welcome to Bytes!

There is nothing wrong with your proposed order lines key, which is indeed composite. All that composite means is that there is more than one component. In this case there are two components, one of which is the primary key for the order and the other the primary key for the item concerned. A linking table of this kind is essential when decomposing one-to-many relationships.

I disagee entirely with blanket statements such as 'avoid composite keys'. In the example of the order lines table you mention, the composite key is exactly the right solution.

-Stewart

[youmike]

Your approach also means that your application will not allow multiple records for the same Order/Product combination. Assume this is what you want?