About Lookup table, lookup fields and composite (multi-field) primary keys.

[bubai]

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?

[June7]

Don't build lookup fields in table, build combobox and listbox on form.

Set compound index on field combinations that cannot be duplicated. Does not have to also be designated as primary key. This will prevent duplicate combinations.

[bubai]

Set compound index on field combinations that cannot be duplicated. Does not have to also be designated as primary key. This will prevent duplicate combinations.

When I select multiple fields the indexed options do not show up. But if I make them indexed separately then it wont serve the purpose. Also, can you describe what Lookup table and what Lookup fields are?

[June7]

Review

http://www.geeksengine.com/article/c...ex-access.html

There is no issue with using lookup tables, just don't build lookup fields in another table that pulls values from lookup table, especially lookups that use alias. This means an ID is saved but descriptive value is displayed. An example of a lookup table would be a Products table. User will see product name/description but ProductID will be saved into OrderDetails table.

Build comboboxes and listboxes on forms.

[alansidman]

https://www.quackit.com/microsoft_ac...ccess_2016.cfm

[bubai]

An example of a lookup table would be a Products table. User will see product name/description but ProductID will be saved into OrderDetails table.

Build comboboxes and listboxes on forms.

Thanks for the indexing link.
But if I name the lookup fields in a way so that it reminds me that it is an ID field, not an alias will it then be OK? Because, either way the ID is saved to the field.

[orange]

You may find the info in this thread helpful --see the materials related to Reference and Lookup Table.

[June7]

Entirely your decision. But if someone else ever has to manage this db, you may be just building in confusion for them.

[Mickjav]

I've added a picture but you should allways give fields names that relate to the information thay will be storeing like JoinDate which indecates the value to be stored is a date.
also giiving the objects I.E. tables or forms a meaningful name will help when designing projects.

hope it helps mick

[ssanfu]

For good measure I'll throw in this about Primary Keys Microsoft Access Tables: Primary Key Tips and Techniques

Also see Microsoft Access and SQL Server Database Normalization Tips
Look for paragraph "Use a Meaningless Field for the Key Field"