Auto-Generated Composite Key? Not sure.
My project is a very simple customer/transaction database. I have 3 tables (Customer, Transactions, ItemFromTransaction). I've autogenerated a serial/primary key for the first two tables (CustomerNumber & TransactionNumber) and set the relationships up appropriately.
There are some transactions that will have a single item, and some that will have upwards of 20 items per transaction. Instead of creating my Transactions table with 30 blank item lines, I created the third table ItemFromTransaction. This seemed the best way to design the table. The issue I'm having is that I'm used to other programs and I'm not sure if there is an option to designate a composite key for my third table. The auto-generated field for the ItemFromTransaction table is the ItemNumber field, but it will not be unique because there will always be an ItemNumber value of '1' (or more) for each TransactionNumber.
I know there has to be a step I'm missing to make the count for ItemNumber reset for each transaction, but I'm not sure what it is. Is there a calculation I'm supposed to use instead? Any help would be appreciated....