In defining MS Access tables where a 1 to many relation exists what is the best way to define a table containing the “many” records. For example, a database with orders and line-items might have the “line item” table with an order-number and a line-number which would define a unique record. Is there a way to create a compound primary key for this with Access 2000?
Since duplicates are allowed, the order number itself can not be a primary key of the line item table, so is it necessary to have a “primary key” or would it be reasonable for such a table not to have a primary key?
Or is it better to simply add an auto-number field to the table?