Tuesday, July 1, 2008
Lookup Tables
Also known as reference, crosswalk and a few other names.
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
Labels: apex, constraints, database, design, development
¶ 7/01/2008 09:52:00 PM
Comments:
Links to this post:
<< Home
Nerd,
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" ?
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).
What is the best way, in your opinion, to enforce this in the database ?
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" ?
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).
What is the best way, in your opinion, to enforce this in the database ?
@narendra
For me, this would be an application (ApEx) issue.
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.
As far as history, I wouldn't touch the old ones. I'd just leave 'em be.
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).
Putting it in the application layer where the user only sees "effective" address types would be my suggestion.
chet
For me, this would be an application (ApEx) issue.
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.
As far as history, I wouldn't touch the old ones. I'd just leave 'em be.
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).
Putting it in the application layer where the user only sees "effective" address types would be my suggestion.
chet
Nerd,
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.
Anyway, thanks again.
Post a Comment
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.
Anyway, thanks again.
Links to this post:
<< Home
