Database Design – Steve Ngai (危志荣)

One Resource Table
Using one resource table method is similar to many lookup tables. The difference is this one resource table actually combines all lookup tables by adding just one more field. Of course, this one resource table will be much larger because it combines all lookup tables.

Using back the same lookup tables at previous blog, let’s create one resource table to replace all lookup tables.

Type	Code	Desc
State	JH	Johor
State	KH	Kedah
State	KT	Kelantan
State	MK	Melaka
State	NS	Negeri Sembilan
State	PH	Pahang
State	PG	Pulau Pinang
State	PK	Perak
State	PS	Perlis
State	SB	Sabah
State	SW	Sarawak
State	SL	Selangor
State	TG	Terengganu
State	KL	W.P. Kuala Lumpur
State	LB	W.P. Labuan
State	PJ	W.P. Putrajaya
Country	CN	China
Country	HK	Hong Kong
Country	IN	India
Country	MY	Malaysia
Country	SG	Singapore

That’s it. Use both type and code field to make the primary/unique key.

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.