This content has been marked as final. Show 5 replies
If you have a generic lookup table (a bit like the domain table cg_ref_codes), you have a more complicated data model. The primary key of a table with columns: [lookuptype, lookup_code, lookup_value, lookup_description] will be [lookuptype, lookup_code]. Consequently, all your tables that reference this table need to have a foreign key [lookuptype, lookup_code].
My opinion? Every entity should have it's own table.
As a note: I don't even think that your table definition [lookuptype, lookup_code, lookup_value, lookup_description] is correct. It shouldn't it be [lookuptype, lookup_code,lookup_description]?
'CITY',1,'New York' 'CITY',2,'Washington' 'COLOUR',1,'Red' 'COLOUR',2,'Yellow'
Thanks for your reply;
you are right regarding the table structure, there is no lookup_value column, but regarding the primary key problem, cant we just add a seq coulmn to act as a primary key?
You do that and you run the risk of different spellings for the same place e.g.
Edited by: Johnreardon on Apr 17, 2013 2:45 AM
cant we just add a seq coulmn to act as a primary key?You are right, you can use a technical PK.
Still, I don't think it is a good idea. Looking at CITY for instance, you probably also want to add state code, country code, area code etc. to CITY.
I think that what you want is already standard functionality in Designer in the form of Domains (and the table CG_REF_CODES). However, there is a big difference between a domain and an entity. There is also a big difference in how Designer uses domains and lookup tables when generating Forms.
I don't use Designer to generate forms, but use it heavily to clean up table designs. I just wanted to add that I much prefer to create separate lookup tables for each lookup-type.
The option of mixing them together in the CG_REF_CODES works okay for small data set until you want to add some large lookup lists or until a list needs to be updated. Then maintenance of a volatile list can become cumbersome.
The other option of putting attribute values in a check-constraint is even more of a maintenance headache. Persistent lookup lists never prove to be as consistent as predicted - so I plan for maintenance early in the design stage.
I know this results in a myriad of small lookup tables but they each serve their own purpose and have their own foreign-keys. When joining them to the users tables, there no difference in performance - just easier maintenance.
I ALWAYS put a single-column, system-generated Primary key on all tables (as a key NOT DESIGNED for users to manage) and manage it through triggers. If the users have other columns that need uniqueness, singly or in combination, I put them in a Unique key. I've used this strategy throughout many designs.