I hope someone can help. I'm trying to set up something along the below but am getting in a bit of a muddle. From what I understand, deriving a numeric ID variable (eg auto-incremented) for the primary key is more efficient that using a composite primary key of the 'natural' variables that define a record (especially if they're character variables (and more so if the collation is UTF-8))
As in the below example, each customer has a list of items (ITEMID) which are all members of a category (CATID), however, the problem is that I need customers to additionally be able to assign their items as a component of a collection set (SETID) which is a non-identifying reference table - any customer could have multiple versions of one SETID.
The items required for a set are specified by CATID. Therefore, in the example below which is for one customer, they could choose to assign Item 2, or 4 (or neither or both) to SET 001.
**ITEMS**
ITEMID CATID
1 04
2 02
3 01
4 02
5 05
**SETS**
SETID CATID
001 01
001 02
002 04
003 05
**CATEGORY**
CATID
01
02
03
04
05
**Wanted result:**
ITEMID CATID SETNUMBER SETID
1 04 (customer chose not to assign to SET 002)
2 02 1 001
3 01 1 001
4 02 2 001
5 05 003
Many thanks in advance!
