How to go about this?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

How to go about this?

Post by recci »

Ok I’m developing a component for a CMS to help manage honours degree projects at a university (This is my honours project).

Because im using a CMS (Joomla) certain functionality like user registration and login is already taken care of as well as a basic user group system that can be used to define roles etc...

However i need to capture additional about the two main types of users which are: Lecturers and students. The crux of the problem is both students and lecturers will need to have very different information stored about them so I assume ill need two tables that both have a 1 to 1 relationship to the existing joomla users table.

Students need the ability to create a short-list of proposals they are interested in. So that's another table in a 1 to many relationship with the additional student_info table.

And there has to be a way to allow me to assign a student to a Lecturer but a lecturer can have multiple students assigned to him/her so there is another table required.

I'm just basically looking for some advice on how to model the database structure for this in an reasonably efficient way..once i understand how the logic of this should work programming it will be straightforward.


Just to try and explain this a bit further the basic functional requirements of the system are as follows:

• Enable staff to upload project proposals according to a template;
• Enable the Projects Co-ordinator to check proposals for duplicates;
• Enable students to browse, search and download project proposals;
• Enable students to prioritise interest in projects;
• Enable students to submit self proposed projects;
• Assist the Project Co-ordinator in allocating projects to students;
• Assist the Project Co-ordinator in tracking student progress throughout the year.

I already have my proposals table finished and the submit and searching of proposals working.

The reason i say that students and lecturers require very different information stored about them is because of the requirement that involves in tracking student progress throughout the year this will be quite a big table.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to go about this?

Post by JakeJ »

Assigning multiple students to one lecturer is pretty straightforward.

Create a lectures table with at least the following fields (adapt to your own naming convention of course).

lectures:
id
lecturerid //the id of the lecturer from the lecturers table
studentid //the id of the student from the students table

Using this structure, you can have multiple students assigned to multiple lecturers and of course multiple students assigned to one lecturer.

If you eliminate the id from the lectures table and just use the lectureid and studentid and use that combination to create a unique index then you can be assured that a student does not get assigned to the same lecturer twice. Of course if the students NEEDS to be assigned to the same lecturer twice on occasion, then stick with the id to ensure uniqueness.

I hope this helps.
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: How to go about this?

Post by recci »

I have been thinking about it and came up with:

four tables: students, lecturers, proposals, and interests.
- Students has whatever, plus a FK off to whoever is lecturing them and a FK to the main user table
- Lecturers has whatever and a FK to the main user table
- Proposals has whatever, plus a FK off to whoever proposed it
- Interests has two FKs (student and proposal) and some number measuring the student's interest level

The lecture can have many students but the student can only have one lecturer as his supervisor.

Does this look workable?

Thanks Jake
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to go about this?

Post by JakeJ »

I'm not sure what the proposals table is for so I can't comment on that.

Seriously though, you should have a table solely for students assigned to lecturers. You could end up with a student assigned to multiple lecturers and you'd have to reprogram a LOT to accommodate the change. It's just better design to do as I suggested. Also, if the assignment changes, you'll have a record of who it was assigned to before. You can flag active and inactive lecture assignments, etc. Keep it flexible even if it's a little more work up front.
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: How to go about this?

Post by recci »

You could end up with a student assigned to multiple lecturers
I dont see how. with one FK to the on the student table they can only have one lecturer but the lecturer can have many students
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to go about this?

Post by JakeJ »

I don't know how either :wink: But good data design should be followed anyway.
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: How to go about this?

Post by recci »

I was coming round to your suggestion anyway :lol:
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to go about this?

Post by JakeJ »

Great, and if I have any bad ideas, please correct them. I've had no shortage of them in the past. :lol:
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: How to go about this?

Post by recci »

Does this resemble the idea you had. Do the relationships look correct. The PK of each table is always just id in joomla. and the shortlist table is for students to compile a shortlist of possible projects before they are assigned to one.
Attachments
main.jpg
main.jpg (71.42 KiB) Viewed 3185 times
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: How to go about this?

Post by JakeJ »

Looks pretty solid to me! Good job.
Post Reply