tag:blogger.com,1999:blog-8884584404576003487.post3709393898442445729..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: Lookup Tablesoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-8884584404576003487.post-45575080998788784582008-07-03T14:28:00.000-04:002008-07-03T14:28:00.000-04:00Nerd,Thanks for your reply. I agree that this can ...Nerd,<BR/><BR/>Thanks for your reply. I agree that this can be easily implemented in the application. But as I am completely hypnotized by Tom's thought process, I was wondering if I can enforce this in database itself.<BR/>Anyway, thanks again.Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-6117766281155310532008-07-02T20:53:00.000-04:002008-07-02T20:53:00.000-04:00@narendraFor me, this would be an application (ApE...@narendra<BR/><BR/>For me, this would be an application (ApEx) issue.<BR/><BR/>For the List of Values, I would include the filter, "WHERE effective_to IS NULL" so that users would only be able to see those that are active and can only INSERT those.<BR/><BR/>As far as history, I wouldn't touch the old ones. I'd just leave 'em be.<BR/><BR/>Now, you could twist yourself in circles (like I seem to do frequently) and use triggers, materialized views and other such stuff (I really don't know how MVs would help, I just felt like saying it).<BR/><BR/>Putting it in the application layer where the user only sees "effective" address types would be my suggestion.<BR/><BR/>chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-28814941896470345762008-07-02T14:36:00.000-04:002008-07-02T14:36:00.000-04:00Nerd,I was waiting for somebody to write on this t...Nerd,<BR/><BR/>I was waiting for somebody to write on this topic. I have few doubts about Lookup tables. As Tom says (and also you have mentioned), we should enforce as much data integrity constraints in database as possible. Now, if I understand correctly, you are suggesting that the table containing data (e.g ADDRESSES) should have a foreign key defined, pointing to the lookup table (e.g. ADDRESSTYPE Lookup). While I agree with that, I am not quite sure what is the best way to enforce data integrity in the database for cases where LookupCodes "age out" ?<BR/>For e.g. if I have 3 values in ADDRESSTYPE lookup table, namely HOME, OFFICE and OTHER and I want to provide the ability to activate/deactivate the ADDRESSTYPE codes, I include EFFECTIVE FROM and EFFECTIVE TO columns in the ADDRESSTYPE lookup table. Initially, EFFECTIVE TO will be NULL for all 3 types, meaning all 3 are active and can be used in ADDRESSES table. Also, ADDRESSES table has foreign key relation with ADDRESSTYPE table. Now, if user wants to "deactivate" one of the ADDRESSTYPE codes, say "OTHERS", for future use, she will enter and EFFECTIVE TO date for "OTHER" ADDRESSTYPE record. But, what is the best way to ensure that ADDRESSES has foreign key constraint with only "ACTIVE" ADDRESSTYPE lookup values (for data-entry) ? User may still need ability to "see" old records that once used the now deactivated lookup codes (e.g. ADDRESS records of type OTHER).<BR/>What is the best way, in your opinion, to enforce this in the database ?Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.com