Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normalization Issue

    Hello,

    I am trying to normalize my db and am unsure of the most efficient way to structure a particular table, which I have listed below.

    Student table
    ID
    First_Name
    Last_Name
    D.O.B
    Gender
    Year_Level

    My problem is this, when normalizing a db I should not have repeating groups so I am wondering does that extend as far as the field "Gender" in that I will be repeating "male" and "female" values in that field every time?

    In other words, do I need to have a separate table for Gender and then put a foreign key in the Student table linking the Gender and Student tables together, or is it ok to just have the Student table layout I have listed above and set rules permitting the data to be entered into the Gender field?

    Irish87.

  • #2
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also just to clarify, if I where to add to my db a field, for example, "Subjects_Taken" where the number of subjects could be more than one and the number of students a subject can be taken by can be more than one, would I need to build a separate table as shown below:

    Student table
    Student_ID
    First_Name
    Last_Name
    D.O.B
    Gender
    Year_level

    Subject table
    Subject_ID
    Subject_Type

    Student_Subject_Taken table
    Student_ID
    Subject_ID

    Would this be the most efficient way to layout my database after normalizing it?

    Irish87.
    Last edited by irish87; 07-30-2011 at 10:06 AM. Reason: Incorrect layout

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Everything you've got in your bottom post is fine. You could normalize the gender into separate table but that really isn't necessary.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    Even a purist would be hard pressed for making a case that gender be in a separate table.

    After all, gender char(1) will occupy less space in your DB than genderID int references genderTable(genderID) (1 byte versus at least 4 bytes, not to mention the separate table).

    As a rule of thumb, I don't see the need to normalize fields that take up less space when not normalized, though there are the occasional exceptions.

    Anyway, if you were going to normalize gender in that table, why wouldn't you also normalize Year_Level? Would make just as much (if not more) sense.

  • #5
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Guelphdad: Thanks for your help. Much appreciated.

    Old_Pennant: Thanks for your help Also you make a good point about the year_level. I guess for those two fields (gender and year_level) I can just create drop downs instead of a textbox (when creating the front_end) and set those drop downs to show either "male" or "female" and likewise for year_level?

    Cheers,

    Irish87.

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again,

    I have just noticed something else that I am struggling to come to grips with regarding normalization. If I have a table that has a concatenated key, I need to get rid of that concatenated key by creating a many to many table, that part I understand. The part I do not understand is which table all the remaining fields go into after that.

    Here's an example:

    If I had a table regarding injuries it might look something like this:

    Injury_ID (PK)
    Site_of_Injury_ID (PK)
    Date_of_injury
    Diagnosis
    Recovery_Period
    Rehabilitation
    Limitations
    Review_Date
    Notes
    Return_Date

    This table shows the concatenated key. Ok, so now when I split this table into a many to many table (in this example an injury could have different sites hence the concatenated key) I am getting confused.

    If some could explain this particular process I should be able to work it out. In this example, this is what I would have suggested.

    Tbl_Injury_Site
    Injury_ID
    Site_ID

    Tbl_Site
    Site_ID
    Site_Description

    Tbl_Injury
    Injury_ID
    Date_of_Injury
    Diagnosis
    Recovery_Period
    Rehabilitation
    Limitations
    Review_Date
    Notes
    Return_Date

    The place where I am getting confused is should I have fields like "Diagnosis", "Rehabilitation" and/or "Limitations" etc... in the table Tbl_Injury_Site or just leave them where they are in Tbl_Injury? (as you could say that they all depend on Site_ID as well as Injury_ID)

    Cheers,

    Irish87.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    I think you have to decide that issue.

    Suppose that an incident has multiple sites. One in the field, one at the hospital? And in the field the diagnosis is fracture. But in the hospital X-rays reveal no fracture.

    If you need to retain both diagnoses, then clearly the diagnosis must be associated with the site.

    Tbl_Injury_Site
    Injury_ID
    Site_ID
    Diagnosis_ID

    But if you only need to retain a single overall diagnosis, then it should stay with the injury information.

    Don't assume that all DB design decisions depend solely on best DB practices. Sometimes (many times!) the design has to depend on what the data is supposed to represent.

  • #8
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again Old Pedant. That breaks things down and makes things a lot clearer.

    Irish87.

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    I guess for those two fields (gender and year_level) I can just create drop downs instead of a textbox (when creating the front_end) and set those drop downs to show either "male" or "female" and likewise for year_level?
    Drop downs are better at ensuring fewer mistakes on inserting the data. I'n not sure what data would be in year_level but, whether using a db table to populate it or your code, make sure its dynamic. ie, the range of years should be suitable for your app, whether the system is being used now or in ten years.

    pm me if you like for a better explanation.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •