sql server - SQL efficient solution for mutilple joins (could be 10 joins) on same table (lookup table) -

we've implemented lookup table contains id , text field. table consists of lookupid different field values. example...

table

• tableid, statusid, typeid, docid

values this

• 1, 2, 3, 4

lookuptable

• lookupid, text

values this

• 1, somevalue
• 2, status text
• 3, type text
• 4, document text

what need know if efficient solution query on these tables (creating sql view of data text displayed instead of id)

select t.tableid, l1.text statustext, l2.text typetext, l3.text doctext 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 

... or subqueries work better solution? this.

select t.tableid,   (select l.text lookuptable l l.lookupid = t.statusid) statustext,   (select l.text lookuptable l l.lookupid = t.typeid) typetext,   (select l.text lookuptable l l.lookupid = t.docid) doctext table t 

... or there better solution? keep in mind joined 3 times example , need join 10 or more times.

your best bet create separate lookup tables each separate entity in database. gives better flexibility in future, should need add additional attribute single lookup type (for example, need track state birds each state, won't relevant car models). in experience "generic" database design patterns turn out bad. design purpose.

once have that, long have appropriate indexes having multiple joins (if not always) perform better subqueries:

select     p.person_id,     s.state_name,     g.movie_genre_name,     ...     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 every list in system doesn't have lookup table. gender, example, can maintained through check constraint:

gender varchar(15) null constraint chk_person_gender check (gender in ('male', 'female', 'transgender')) 

or:

severity varchar(10) not null constraint chk_ticket_severity check (severity in ('high', 'medium', 'low')) 

this relevant lists name. lists have items additional attributes or lists might change on time should put table.