07-30-2011, 09:50 AM
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.
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?
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:
Would this be the most efficient way to layout my database after normalizing it?
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.
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.
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?
07-31-2011, 05:06 PM
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:
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.
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)
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.
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.
08-01-2011, 06:44 AM
Thanks again Old Pedant. That breaks things down and makes things a lot clearer.
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. ;)