Re: Coding "missing values" - standards, customs, tricks of the trade?

This idea is from one of Joe Celko's books: If you are using numerical codes for any purpose, either for coded data (1=male,2=female,3=male->female sex-change,...) or for different types of missing data, you should store verbal descriptions in a separate table. Then you can force documentation of every new code value added via a trigger that barfs if there is no row in table "codes" corresponding to that combinition of data table, data field, and data value. This just goes back to the fundamental concept that you want metadata stored in the same system as data, so it can be queried in the same manner. A simple "codes" table might look like the following: TABLENAME FIELDNAME CODE_VALUE CODE_TEXT DATE_CODE_ADDED ADDED_BY REASON_ADDED Or, if having one CODES table be a "hot spot" is an issue, you might have a number of similar tables, each called something like EmpDetail_CODES, DeptDetail_CODES, etc (one foo_CODES table per data table) and looking like: FIELDNAME CODE_VALUE CODE_TEXT DATE_CODE_ADDED ADDED_BY REASON_ADDED I'd also suggest having a strict policy in place regarding who can add codes for what reasons -- you don't want uncontrolled proliferation of reasons for missing data! If there is continual pressure for new types of missing data, that should raise a red flag about the overall project: either you need to improve your data collection, or you need to ask whether a given field really needs to be there at all. Many of the explanations I've heard for cases where some field rarely has a real value boil down to (1) nobody cares about that field, or (2) that information is virtually impossible to obtain when demanded, or (3) everyone knows the likely value of that field from other fields in the record, so it's basically redundant. -------- Matthew.Healy@yale.edu Postdoc (& now, WebMaster) Center for Medical Informatics, Yale School of Medicine &X1A3B4603089.410DBE658538FD9E2504 Furlongs/Fortnight It's not just a good idea, it's the law! http://paella.med.yale.edu/~healy/matt_healy.html