...

View Full Version : Normalization Issue



irish87
07-30-2011, 09:50 AM
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.

irish87
07-30-2011, 10:02 AM
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.

guelphdad
07-30-2011, 05:03 PM
Everything you've got in your bottom post is fine. You could normalize the gender into separate table but that really isn't necessary.

Old Pedant
07-30-2011, 10:42 PM
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.

irish87
07-31-2011, 04:02 PM
Guelphdad: Thanks for your help. Much appreciated.:thumbsup:

Old_Pennant: Thanks for your help :thumbsup: 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.

irish87
07-31-2011, 05:06 PM
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.

Old Pedant
08-01-2011, 01:05 AM
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.

irish87
08-01-2011, 06:44 AM
Thanks again Old Pedant. That breaks things down and makes things a lot clearer.

Irish87.

bazz
08-02-2011, 01:43 AM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum