data modeling - Why use lookup tables in a database -

this theoretical question ask due request has come way recently. own support of master operational data store maintains set of data tables (with master data), along set of lookup tables (which contain list of reference codes along descriptions). there has been push downstream applications unite 2 structures (data , lookup values) logically in presentation layer easier them find out if there have been updates in overall data. while request understandable, first thought should implemented @ interface level rather @ source. combining 2 tables logically (last_update_date) @ ods level similar de-normalization of data , seems contrary idea of keeping lookups , data separate. said, cannot think of reason of why should not done @ ods level apart fact not "seem" right... have thoughts around why such approach should or should not followed?

i listing example here simplicity's sake.

data table id    name    emp_typ_cd  last_update_date 1     x       e1          2014-08-01 2     y       e2          2014-08-01  code table emp_typ_cd     emp_typ_desc    last_update_date e1             employee_1      2014-08-23 e2             employee_2      2013-09-01 

the downstream request represent data as

data view id    name    emp_typ_cd  last_update_date 1     x       e1          2014-08-23 2     y       e2          2014-08-01 

or

data view id    name    emp_typ_cd  emp_typ_desc   last_update_date 1     x       e1          employee_1     2014-08-23 2     y       e2          employee_2     2014-08-01 

you correct, demoralizing database because wants see data in way. side effects, know, duplicating data, reducing flexibility, increasing table size, storing dissimilar objects together, etc. correct problem should solved somewhere or somehow else. won’t want if change database way want change it. if want make “easier them find out if there have been updates in overall data” duplicate massive amounts of it, they’re opening errors. in example emp_typ_cd updated value must updated employees emp type code. update statement that, still, instead of updating single row in lookup table you’re updating every single employee has emp type.

we use lookup tables time. can add new value lookup table, add employees database fk new attribute, , report joins on table has id, value, sort order, etc. let’s add ‘veteran’ lu_work_experience. add employee veteran fk_id , existing query joins on lu_work_experience has value. sort work experience alphabetically or our pre-defined sort.

there valid reason flattening data structure though, , speed. if you’re running large report faster joins (and indexing). if business knows it’s going run large report many times , worried end user wait times, idea build single table 1 report. time calculated measures. if know analytic report have ton of aggregation , joins pre-aggregate data data store. being said, don’t in sql because use cubes analytics.

so why use lookup tables in database? logical separation of data. employee has employee code, not have date of when employee code updated. reduce duplicate data. minimize design complexity. avoid building table specific report , having build different table different report if has similar data.

anyway, rest of argument comprised of facts database normalization wikipedia page i’ll skip it.