Product table with possible duplicate part numbers from different manufacturers I am making a database. It has a product(aka component or part) table with a ID, ManufacturerID, and PartNumber. The ID is obviously the primary key and is unique. The ManufacturerID is pulled from the ManufacturerT. The PartNumber will be entered by the user. I am trying to decide whether to make the PartNumber field unique or not. If I do, I won't have the issue with a user entering the same product multiple times, but what if there is a product from a different manufacturer with the same PartNumber? It wouldn't allow the user to enter. What is the solution to this? Does it require some special logic? Is there a way to not allow duplicates if the ManufacturerID and PartNumber fields are identical? I am using Microsoft Access 2013(version 15).
A common solution to this problem is composite keys or indices. You can specify any key or index as unique, so combining the PartNumber and ManufacturerID into a composite and then marking it as unique has the effect of enforcing the uniqueness of each combination of PartNumber and ManufacturerID. See this thread on SO for more information.