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 ?
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]?
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.