We've implemented a lookup table that contains an ID and a Text field. My table then only consists of the LookupID for different field values. For example...
SELECT T.TableID, L1.Text as StatusText, L2.Text as TypeText, L3.Text as DocText
FROM Table T
LEFT JOIN LookupTable L1 on L1.LookupID = T.StatusID
LEFT JOIN LookupTable L2 on L2.LookupID = T.TypeID
LEFT JOIN LookupTable L3 on L3.LookupID = T.DocID
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.StatusID) as StatusText,
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.TypeID) as TypeText,
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.DocID) as DocText
FROM Table T
Your best bet is likely to create separate lookup tables for each separate entity in your database. This gives you better flexibility in the future, should you need to add an additional attribute for a single lookup type (for example, now you need to track state birds for each state, but that certainly won't be relevant for car models). In my experience "generic" database design patterns usually turn out bad. Design with a purpose.
Once you have that, as long as you have the appropriate indexes having multiple
JOINs will almost always (if not always) perform better than subqueries:
SELECT P.person_id, S.state_name, G.movie_genre_name, ... FROM Person P INNER JOIN [State] S ON S.state_id = P.home_state_id INNER JOIN Movie_Genre G ON G.movie_genre_id = P.favorite_movie_genre_id ...
Also, keep in mind that every list in your system doesn't have to necessarily be a lookup table. Something like gender, for example, can be maintained simply through a
gender VARCHAR(15) NULL CONSTRAINT CHK_Person_Gender CHECK (gender IN ('Male', 'Female', 'Transgender'))
severity VARCHAR(10) NOT NULL CONSTRAINT CHK_Ticket_Severity CHECK (severity IN ('High', 'Medium', 'Low'))
This is relevant for lists that are basically just a name. Lists that have items with additional attributes or lists that might change frequently over time should be put into a table.