Categories
Database Design

Reasons for database de-normalization

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:

  • Data Integrity (Security). Changes of data in other tables will not affect the master table.
  • Performance. When you join with many tables, performance will be affected.
  • Other examples when to denormalize: financial transaction, audit log and statistic.

    References

  • http://www.25hoursaday.com/weblog/2007/08/03/WhenNotToNormalizeYourSQLDatabase.aspx
  • Categories
    Database Design

    One Resource Table

    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.

    TypeCodeDesc
    StateJHJohor
    StateKHKedah
    StateKTKelantan
    StateMKMelaka
    StateNSNegeri Sembilan
    StatePHPahang
    StatePGPulau Pinang
    StatePKPerak
    StatePSPerlis
    StateSBSabah
    StateSWSarawak
    StateSLSelangor
    StateTGTerengganu
    StateKLW.P. Kuala Lumpur
    StateLBW.P. Labuan
    StatePJW.P. Putrajaya
    CountryCNChina
    CountryHKHong Kong
    CountryINIndia
    CountryMYMalaysia
    CountrySGSingapore

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

    Categories
    Database Design

    Hardcoded Values vs Lookup Tables

    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?

    IDNameAgeAddressStatePresent_CountryCountry_Born
    1Steve2312-C-5Wilayah PersekutuanMalaysiaSingapore
    2Ngai2111-B-5SelangorMalaysiaMalaysia
    3Chee Weng2412-A-1PahangMalaysiaHong 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_CodeState_Desc
    JHJohor
    KHKedah
    KTKelantan
    MKMelaka
    NSNegeri Sembilan
    PHPahang
    PGPulau Pinang
    PKPerak
    PSPerlis
    SBSabah
    SWSarawak
    SLSelangor
    TGTerengganu
    KLW.P. Kuala Lumpur
    LBW.P. Labuan
    PJW.P. Putrajaya

    Country_CodeCountry_Desc
    CNChina
    HKHong Kong
    INIndia
    MYMalaysia
    SGSingapore

    (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,

    IDNameAgeAddressStatePresent_CountryCountry_Born
    1Steve2312-C-5KLMYSG
    2Ngai2111-B-5SLMYMY
    3Chee Weng2412-A-1PHMYHK

    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.