thara Posted June 20, 2014 Share Posted June 20, 2014 I have designed a database for my institute. Here I have attached my design for better understanding. I am expecting your reviews who are professional for the database design. Thank You. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 20, 2014 Share Posted June 20, 2014 Without knowing the precise relationships between your entities it is impossible to say whether it is correct or not. Also without knowing what processes you require and what you expect to produce from the data there is no way of knowing if it is fit for purpose. The best I can do it to say it looks reasonable. Quote Link to comment Share on other sites More sharing options...
thara Posted June 20, 2014 Author Share Posted June 20, 2014 (edited) @Barand, This is an explanaiton about my relationships I am going to use this database for an institute. In the institute bassically admin can add lectures. To add lectures admin need to login to the system. To register particular lecture admin need : lecture name, address1, address2, contact no (mobile, home), subject category, subject, lecturing medium and classes who is conducting. one lecture can conduct many classes. A particular class should have - class type - Eg: Grade 09, Grade 10 etc.. class date - class start time - class end time - Sometimes a lecture may use more than one medium in their lecturing. Eg: English, Japanese, Chinese etc.. Every subject shoud partian to one category. Thats why I have used "category", "subject" and "category_subject" tables. A lecture can teach only one subject. Students and parents also can log into the system. Thays why I have added "login_type" in login table. Any I have decided to keep all contact detials in one table. All mean (Lectutes', students' and parents') To contact table I will ask - address1, address2, city, email, mobile, home phone, web url etc... Edited June 20, 2014 by thara Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 20, 2014 Share Posted June 20, 2014 I concur with Barand. We would have to have a deep understanding of the structure and process to give good advice. But, here are some comments/questions Why does the lecture table have fields for First Name & Last Name? Wouldn't the lectures be associated with a user or contact record? I would think (guess) the user/contact records should have the name values and the lectures table would just have a foreign key reference to that record. A lecture can teach only one subject Then why is there a child table "lecture_subject_category"? Can't those fields be included in the lectures table? Every subject shoud partian to one category Can the subject-category combinations be different for each lecture? I.e. Is Subject A always in Category B? Or can Subject A be associated to different Catregories in different lectures. If the former is true, then the Subject/Category association should be defined in a separate table and you would only need to include the subject ID in the lectures table. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 20, 2014 Share Posted June 20, 2014 Why does the lecture table have fields for First Name & Last Name? Psycho, I think the "lecutures" table in the diagram is the "lecturer" table thara, from what you stated above Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 20, 2014 Share Posted June 20, 2014 (edited) Psycho, I think the "lecutures" table in the diagram is the "lecturer" table Good catch. If that's the case then the table is named incorrectly. lectures: this is plural form for lecture (i.e. a verbal lesson) lecturers: this is the plural form for a lecturer (i.e a PERSON that performs a lecture) EDIT: I just looked at the table names again and I thought I had misread it due to it being in French or another language. But, it looks like the spelling is just wrong in multiple places - not even consistently incorrect?! lecutures: should be lecturers lecuter_medium: should be lecture_medium lecture_subject_category: This one is right As for field names: lecture_id: Used in three places. It is spelled correctly, but should really be lecturer_id lecuture_id: Used in the "lecuter_medium" table. Edited June 20, 2014 by Psycho Quote Link to comment Share on other sites More sharing options...
thara Posted June 21, 2014 Author Share Posted June 21, 2014 First of all, I would like to apologize you for my broken english and spelling mistake. Then why is there a child table "lecture_subject_category"? Can't those fields be included in the lectures table? Psycho, Yes now I understood that the table of "lecture_subject_category" is not necessary to have. Barand also having got rid of it from his modified version of this design. Thank you for pointed it out me. Barand, You have removed "category_subject" table from your design. So just assume if same subject have in defferent category. Then what happen? then the "subject" table becomes redundant, isn't it? is not it problem? (Here I mean one subject may have under many categroies.) and "user" or "login"? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 21, 2014 Share Posted June 21, 2014 Every subject shoud partian to one category. Which is it? Can a subject belong to more than one category, or only one category Quote Link to comment Share on other sites More sharing options...
thara Posted June 21, 2014 Author Share Posted June 21, 2014 Again sorry for my english. Barand, A subject may belong to more than one category. But not every subjects. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 21, 2014 Share Posted June 21, 2014 In that case I have added back the category_subject table. I don't know if you need them but I also added in the enrollment of students on to courses (where "course" is a series of classes) Quote Link to comment Share on other sites More sharing options...
thara Posted June 22, 2014 Author Share Posted June 22, 2014 Thank you Barand. I highly appreciating your help. One question, What is the meaning of "parentid" in "Student" table? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 22, 2014 Share Posted June 22, 2014 To associate the student's parent with the student Quote Link to comment Share on other sites More sharing options...
thara Posted June 24, 2014 Author Share Posted June 24, 2014 Thank you your help. Can I know what is the tool that you have used to create above diagram? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 24, 2014 Share Posted June 24, 2014 I used the Data Modeller in MySQL Workbench Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.