This primarily refers to variable text fields but in all of the CRUD systems I’ve ever designed every single page has contained fields that use lookup dialogs. Their sheer ubiquity makes their design and operation particularly important significantly impacting an applications feel to users.
In principle sourcing the values for a lookup appears to be a very simple choice.
As you design more systems you start to see further important options
Some general rules I follow;
1)If the application allows users to bulk import data into a table consider using referencing to a distinct values list sourced from a self referencing query of the table.
2)Almost NEVER alias values / use foreign keys in tables that will receive imported data but often better not to use it even if there is no import facility.
3)The one exception to rule 2 can be customer account codes – account codes for clients can be incredibly important and often link to a table that specifies say a company name and address – things that may change in time. Users really need to be trained to understand the derivation of foreign keys to use systems or the codes will generally break import routines or break referencing and require correction. Account codes tend to be a weird case of a derived surrogate where the value is obviously derived from the lengthened value – e.g AMZN for Amazon or MSFT for Microsoft in NASDAQ listings. This works for NASDAQ because of its ubiquity and because NASDAQ has educated its users be prepared that you will need to educate your users if you use this pattern. For this reason my general rule is only use foreign key aliases for Account codes.
4)If you have a field or table that will receive values which may be the subject of a lookup – don’t be too harsh with the field validation in the database data definition and be more permissive with the field variable type and length than you might expect.
And here is suggested SQL that can be used to identify distinct values in a field.
SELECT DISTINCT account_code FROM table_name;