5 Replies Latest reply on Apr 22, 2013 7:37 PM by Rod Davidson

    Lookup tables DB design

      Hi all;
      I just want to ask DB design experts on which approach is better for saving lookup data (cities, colors,.....), is it better to save them all in one large table with the following meta data [lookuptype, lookup_code, lookup_value, lookup_description], or to save each lookup type on a separate table ?
        • 1. Re: Lookup tables DB design
          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'
          • 2. Re: Lookup tables DB design
            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?
            • 3. Re: Lookup tables DB design
              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
              • 4. Re: Lookup tables DB design
                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.
                • 5. Re: Lookup tables DB design
                  Rod Davidson
                  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.