Database Relationship Design

[agnelodsouza]

Hi All,

I'm in need of some assistance: I'm designing a database which consists of 3 tables: Faculty, Courses and then a table which captures data for each module within the relevant faculty and course. I've attached a screenshot of the relationship I have set up at the moment and I have a persistent nag that something is wrong! I would appreciate any comments on improvements to any aspects of the current design.

Thanks !

Agnelo

 

[MarkK]

hey there and welcome

What are you trying to achieve? This looks sort of like a course catalog and if that is your purpose, then the FacultyID should probably not be in the course table. The course needs to be able to exist independent of any person.

And I would not link to the course from the data. The data describing the course IS the course in respect to your system, so the table describing the details of the course should be called tblCourse.

And that's probably enough for starters.

 

[agnelodsouza]

Hey thanks for the reply.

Clarification : Faculty represents a "school" of a particular subject. For example, Faculty of Law , with multiple courses under the law faculty and then a table for the data of the different modules within a particular Law course.

 

[agnelodsouza]

Here's a copy of the new relationship design that I'm using.

 

[jdraw]

Your table vertical data needs some work in my view.

There is a free data model that may help with your thoughts/ideas at
http://www.databaseanswers.org/data_models/university_course_catalogs/index.htm

But note the model is meant to be a generic starting point, that you can use/alter/customize as necessary.

Good luck with your project.

 

[MarkK]

To me CourseName and CourseSynopsis should be in the same table. They are dimensions of the same thing. A row in a table describes a discrete instance of a thing, and each field is a dimension of that instance.

 

[catalin.petrut]

My advices:
1. Faculty and Courses should have a "many to many" relationship. That means that you will need a junction table. Why? Becouse a course can be teached on more then one faculty and one faculty can have multiple courses.
2. On the vertical table you have to erase the facultyID field. The relationship betwen Vertical and Courses tables will be made using courseID field.
3. Your db has more tables right? This is presented only for the porpuse of your problem, right?