A design question for parent and child tables

Posted on 2005-05-02
Medium Priority
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
  • 3
  • 2
LVL 13

Assisted Solution

ghp7000 earned 120 total points
ID: 13914541
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 46

Accepted Solution

Kent Olsen earned 180 total points
ID: 13916716

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

ID: 13917798
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!

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 46

Expert Comment

by:Kent Olsen
ID: 13917888

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

ID: 13920504
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 46

Expert Comment

by:Kent Olsen
ID: 13928278

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

Then I'd normalize.  :)


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

864 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