Hello there.
Lookup table and lookup fields are two common tools in designing database. But I cannot seem to distinguish them properly. My understanding is - Lookup table is where the primary key is and Lookup field is where we put the foreign key. But there is a widespread consensus among database experts and bloggers that lookup fields must be avoided. But how can one not be there when another one is there? Moreover, what good is a primary key if it cannot be used as a reference? Can anybody explain in detail?
Apart from this, some say composite (multi field) primary key should not be used in order to AVOID DUPLICATE RECORDS AMONG MULTIPLE FIELDS. Instead, a secondary index for those fields should be created and defined them as unique. But I have multiple fields which I need to be duplicate within the single field but they cannot come duplicate when multiple fields combined. Can you tell me how to do this without using composite primary key?