Lookup tables are very useful, and a good practice, because the database remains the source of data, and you don't spread it between database and application.
The database should be the canonical source of data. Saving enumeration values in lookup tables gives you referential integrity due to the foreign key relationships between the lookup tables and the other tables that use these enumerations. The database becomes the single source of truth as to which items are in those enumerations.
Programming languages like C# allow you to save a string to a column in the database, and then map it easily to an
enum, but then the C# code becomes the single source of truth for that data, but not the rest. The programming code should be the single source of truth for behavior - algorithms. The database should be about data. Using lookup tables allows this separation fully.
Imagine a case where you have two statuses for orders:
So you create an enum in a programming language to represent this:
public enum OrderStatus
Pending = 0,
Fullfilled = 1
Then you add another status:
Cancelled = 2 but you haven't saved a "cancelled" order to the database yet. While cruising around the database tables, you only see "Pending" and "Fullfilled" statuses for orders. Without the programming code available you cannot tell what all the possible values are.
Related to this, I recommend using a "code" column for the primary key - a string, essentially. Something short. When looking at tables in the database you don't have to keep track of numbers and their corresponding representations. You don't need to remember that a status Id of 1 is Pending and 2 is Fullfilled, and 3 is Cancelled. If your status table has these three statuses, and their primary keys are literally "Pending", "Fullfilled" and "Cancelled" then the other tables that utilize those statuses will have foreign keys that make sense to a human as well, without the need to remember the number to status name translation.