A design question for parent and child tables

Posted on 2005-05-02
Last Modified: 2006-11-18
I got a table design from someone else, and I'm not sure if I agree with his approach:

create table t_parent (
    type_id    integer,
    type_name    char(50))

create table t_child (
    col1    integer,
    col2    char(50),
    type_name    char(50))

type_id    type_name
=====   =======
1             type1
2             type2
3             type3

col1    col2      type_name
===   ====   ========
20       sth1     type2
30       sth2     type1
40       sth3     type3

Notice that in t_child, type_name is used instead of type_id. I think it would be better to use type_id instead, but his argument was that in most cases type_name will be used, so if he puts the type_name there, he does not have to do a join when type_name is required in a query. Should I accept his argument? Thanks.
Question by:minjiezen
    LVL 13

    Assisted Solution

    there is no right/wrong answer, the real answer is: "it depends". On the face of it, it doesnt look normalized, but that doesnt necessarily mean its bad, in fact, the answer you received is good. Sometimes its necessary to repeat information stored in other tables fror precisely this reason, especially if the tables are going to grow very large.
    By the way, I added another comment to your post about indexes.
    LVL 45

    Accepted Solution


    I'm going to agree with ghp7000 that "it depends", but in the example that you've shown, the ONLY time that I'd do this is with a performance-critical OLTP.  And even then it "would depend" on a number of key factors.

    If this is a reporting database, then I would expect the child table to contain the type_id.  type_id and type_name in the parent table would both need to be indexed.  Joins on integers are very fast.

    Good Luck,

    Author Comment

    I forgot to mention one thing: It is for a real time system, meaning the data needs to be stored and retrieved quite often. Would that justify to use type_name instead of type_id? Thanks!

    LVL 45

    Expert Comment


    I'd have to know more about the application to make a definitive decision.  You're still in the "it depends" range.  :)

    About how many reads/writes of the child table do you expect per day?  Are they clustered so that you tend to do 1,000 in a short period of time and then nothing the rest of the day?


    Author Comment

    Hello Kent, I just got more info from him: the child table will be read/write quite evenly throughout the day, but not very intensive, i.e., it may be accessed every few minutes. The application will do something on the machine, then read/write some data in the table; and the cycle repeats (the app is also reading/updating other tables as well during the same time, not just this table. So as far as this table is concerned, the frequency of reading/writing is not at a crazy pace).  
    LVL 45

    Expert Comment


    >>the child table will be read/write quite evenly throughout the day, but not very intensive

    Then I'd normalize.  :)


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now