| Author |
Topic |
|
elmish
Starting Member
3 Posts |
 Posted - 2007-04-03 : 06:22:03
|
| Hello,I'm new your site and this database design stuff.I'm trying to design a databse for a school which I can register a student to school and then get information about his grades, his personal info. I make "student", "teacher" .. tables.In the "student" table I also take the birth certificate info.Then I also want to know about teacher's birth certificate info.So the same columns in "student" table for birth certificate info also was included in "teacher" table.Then I take birth certificate info columns(they depent on personal identification number ).At the end I have "student" , "teacher" , "birthCertficate" tables.So I have 1-1 relationship between student-birthCertificate , teacher-birthCertificate.My question is : How much does make sensible this approach? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-04-03 : 09:08:55
|
| You want to collect information about "Entities" and you need to know what attributes belongs to an Entity...Birth Certificates belong to people, not Students for example.So ask yourself what stuff belongs to people? Keep it there. What's stuff belongs to students. Keep that there, etcBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-03 : 10:42:18
|
| Create generic table of People. Put all info common to everyone in there. Relate addresses or phone numbers or everything else to the People table. Birth certificate info will relate to the People table, of course.Then, create a table of Students, which is a 1:1 relation to People. If a person is a student, they will have a matching row in the Students table. Put all information relevant to students in the students table -- grades, graduation date, whatever else and relate all related tables relevent to students to the Students table.Finally, create a table for Teachers, which works in the same way as the students table. Put all info relevant to teachers only in the teacher tables, and relate all tables that are revelant to Teachers only to the Teachers table as well.Now you have no duplication of data and a clear data model.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
elmish
Starting Member
3 Posts |
Posted - 2007-04-04 : 10:23:24
|
| I'm really appreciate for your answers. Thank you :)But I have one more question. In my country when a student register o school , the school also wants child's parent info, ( father or mother or a guardian). This info includes only name , surname, telephone and e-mail. So If I make this design with inhertance , "parent info" table can not include all of the attributes of the "people" table like birth certificate.So now I can take your all suggestions ,Again thanks for answering me. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-04 : 10:58:06
|
| I would personally still store parents as People initially (birth certificate doesn't have to be a required column, does it?) if that makes sense to put them there. If not, then create a Parents table. Then, you create a table that related students to parents, called, say, "StudentParents", with a pk of StudentID/ParentID. This way, a parent can have many students and students can have more than 1 parent.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-04-04 : 11:00:29
|
| why can't you include parents in the person table?You will need anothertable that describes the relation and retains the 2 keys from the person tableAlso, email and phone should be in 2 separate tables...because people can have more than 1 of each...remember...entities...or containers if you will, of like thingsDid you read the link I supplied?And what country is that?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
elmish
Starting Member
3 Posts |
Posted - 2007-04-04 : 15:00:21
|
| I read the link you advice and it helped me to realize some points.jsmith8858 you just have sad what I thought , thank you for your time :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-04-05 : 09:27:20
|
quote:
Originally posted by jsmith8858
I would personally still store parents as People initially (birth certificate doesn't have to be a required column, does it?) if that makes sense to put them there. If not, then create a Parents table. Then, you create a table that related students to parents, called, say, "StudentParents", with a pk of StudentID/ParentID. This way, a parent can have many students and students can have more than 1 parent.- Jeffhttp://weblogs.sqlteam.com/JeffS
Wrong.Do you have to be a student to have a parent?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-05 : 09:35:46
|
| Brett -- it depends on what data you need to store. If you need to store parents for people other than just students, then you do it a different way. How can you say "WRONG!" when you don't know? Do you think he needs to store teacher's parents? Who really knows?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-05 : 10:21:12
|
| Again, I will repeat:it depends on what data you need to store. If you need to store parents for people other than just students, then you do it a different way. How can you say "WRONG!" when you don't know? Do you think he needs to store teacher's parents? Who really knows?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-04-05 : 10:56:11
|
quote:
Originally posted by jsmith8858
Put all information relevant to students in the students table -- grades, graduation date, whatever else
I think we are going to get a violation of first normal form here  (my emphasis) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-05 : 11:30:20
|
quote:
Originally posted by pootle_flump
quote:
Originally posted by jsmith8858
Put all information relevant to students in the students table -- grades, graduation date, whatever else
I think we are going to get a violation of first normal form here (my emphasis)
Agreed, grades would be a different table. bad wording and/or example ... what I wrote just after what you quoted talks about that:quote:
... and relate all related tables relevent to students to the Students table.
- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-05 : 14:56:04
|
| Brett -- what is your problem? You think I am "wrong" to state that what you are modeling and how your data relates determines how you should physically design your tables? Or is there a rule somewhere that says "no table called Parents can ever be related to a table called Students." Feel free to make complete, valid point if you want to debate and/or comment on other's opinions.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-04-06 : 11:00:24
|
| Sure, and let me say this again...you do not need to be a student to have Parents...I need you to supply an example where this is not trueHow many students do you know, or anyone knows (since this is a public forum) don't have parentsAnd if Modeling the world is a critizim....okBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-06 : 11:54:05
|
quote:
Sure, and let me say this again...you do not need to be a student to have Parents...I need you to supply an example where this is not trueHow many students do you know, or anyone knows (since this is a public forum) don't have parentsAnd if Modeling the world is a critizim....ok
Dude, you are losing it. My accounting system doesn't have a place to store the number of hairs on my head, but I have them! in the real world! yet, the accounting system doesn't track this! Is that a bad design? no! Because it doesn't need to track it !!However, if it was a "hair-restoration" system that was tracking patient data and hair growth, then YES, maybe they do need to track that, and of course they would and we'd have a place to store it. But just because it exists in the real world does not mean that your system cares and needs to track it!! It depends on your requirements!!If his system needs to track the parents of people OTHER than students, then he models it appropriately. If he needs to ONLY track the parents of STUDENTS, then he models it appropriately as well. it doesn't matter what exists in the real world if it is not relevant to the system!Please, please tell me you understand this .... i don't know how to make it any clearer ...- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jhermiz
3564 Posts |
Posted - 2007-04-06 : 12:00:22
|
| I'm with Jeff on this one...or it could be one of those systems where someone one day says, "Well your system is so great, it does track parents of the entire country right? Because that's what we are looking to do store every parent in this country into your system..we just love your system"...in this case I'd agree with brett.No one is wrong here, it depends on how sure and how flexible you want to make the system. The more flexible, the more functionality = the more time, money, and work involved. But at the same time you cover your ( Y ) in all cases.It's a question that the original poster needs to consider not you two!! So chill out :).Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jhermiz
3564 Posts |
Posted - 2007-04-06 : 14:31:19
|
quote:
Originally posted by X002548
Hair's on your head?> Jon agreeing with Jeff...I'm doneOh, and good luck...how bout those bruins?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Brett did you read what I posted? I agreed with both of you. If the system only deals with STUDENT PARENTS then Jeff's right. If it includes any kind of parent I agreed with you.Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url] |
|
|
|
Next Page
|
Database design