Consider this case, your site is an e-commerce site and your customer has placed and confirm an order. You don’t need to normalize this table because the data for this table should not be changed (edit or delete). This design provides two advantages:
Other examples when to denormalize: financial transaction, audit log and statistic.
References
Somehow, null will always exists in the database.
Without doing some null checking at your code, you will get an error.
One way to fix it is by using isNull at your query. Basically this check, if counter is null, then a No value string will be assigned to it.
SELECT isnull(counter,’No value’) AS counter FROM test
So, the output will becomes,
| counter |
| 99 |
| 12 |
| 34 |
| No value |
(This example was done using SQL Query Analyzer in SQL Server 2000)
Say you have this table below,
| product_type | log_datetime |
| xbox | 2008-06-11 16:26:46.000 |
| xbox | 2008-06-13 16:30:09.653 |
| psp | 2008-06-14 16:28:08.000 |
| ps | 2008-06-16 16:21:40.000 |
And you would like to display log_datetime in different format, how do you do it? One way is to do the formating at the query side. Example, if you want the date format to be dd mmm yyyy format, use CONVERT to format log_datetime like below
SELECT product_type, convert(varchar(11),log_datetime,106) AS log_date FROM test ORDER BY log_datetime
The output becomes,
| product_type | log_datetime |
| xbox | 11 Jun 2008 |
| xbox | 13 Jun 2008 |
| psp | 14 Jun 2008 |
| ps | 16 Jun 2008 |
If you want to just show the time only, just change the 106 to 108 to become,
SELECT product_type, convert(varchar(11),log_datetime,108) AS log_date FROM test ORDER BY log_datetime
The output becomes,
| product_type | log_datetime |
| xbox | 16:26:46 |
| xbox | 16:30:09 |
| psp | 16:28:08 |
| ps | 16:21:40 |
For more reference, http://doc.ddart.net/mssql/sql70/ca-co_1.htm
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:
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.