Hardcoded Values
You have a form, say, a registration form. In this form, you have these common fields, example Name, Age, Address, State, Country and etc. So, in your database design, how do you go about it? Create just one table to store all these fields? Something like below?
| ID | Name | Age | Address | State | Present_Country | Country_Born |
| 1 | Steve | 23 | 12-C-5 | Wilayah Persekutuan | Malaysia | Singapore |
| 2 | Ngai | 21 | 11-B-5 | Selangor | Malaysia | Malaysia |
| 3 | Chee Weng | 24 | 12-A-1 | Pahang | Malaysia | Hong Kong |
Now, consider these few questions:
In your add registration form, how do you populate those values to State, Present Country and Country Born? Because you just have one table, you have no choice but to hardcode those values.What about edit registration form and view registration form page? How do you populate the values to State, Present Country and Country Born?What happen when tomorrow government declare that Wilayah Persekutuan’s name has changed to KL Persekutuan?To solve the above problems, you need to use an UPDATE query to change all records which has the value Wilayah Persekutuan to KL Persekutuan. Then you need to go to add registration form page to change the hardcoded value, as well as edit registration form and view registration form code. Maintenance nightmare!
Lookup tables
Let’s improve the design by having some lookup tables (the word “lookup” is my own term). First, we identify which fields we should have a lookup tables. I pick State, Present Country and Country Born. So, I need to create two lookup tables. Two because Present Country and Country Born can share the same table. Let’s have a simple design for these lookup tables.
Both fields can just be varchar and below are some sample data
| State_Code | State_Desc |
| JH | Johor |
| KH | Kedah |
| KT | Kelantan |
| MK | Melaka |
| NS | Negeri Sembilan |
| PH | Pahang |
| PG | Pulau Pinang |
| PK | Perak |
| PS | Perlis |
| SB | Sabah |
| SW | Sarawak |
| SL | Selangor |
| TG | Terengganu |
| KL | W.P. Kuala Lumpur |
| LB | W.P. Labuan |
| PJ | W.P. Putrajaya |
| Country_Code | Country_Desc |
| CN | China |
| HK | Hong Kong |
| IN | India |
| MY | Malaysia |
| SG | Singapore |
(View a complete country code)
Right now, you can go back to your add registration form code, and you can use these look up tables to populate instead of using hardcoded values. You can use the State_Code and Country_Code as the value to store in user table, which becomes below,
| ID | Name | Age | Address | State | Present_Country | Country_Born |
| 1 | Steve | 23 | 12-C-5 | KL | MY | SG |
| 2 | Ngai | 21 | 11-B-5 | SL | MY | MY |
| 3 | Chee Weng | 24 | 12-A-1 | PH | MY | HK |
You can also use these lookup tables to populate values for edit and view page.
What happen when you realize that you are going to have a lot of lookup tables? Then, you can consider using just one resource table.