hi i would appreciate some help in designing db tables for the following scenario as i am quite stuck at the moment:

I am building this db for a university module reviewing system. There are many modules each having staff assigned to them in different roles, such as internal examiner, moderator, and other examiners. I would therefore want to represent these in tables so that i would be able to extract information about who are the staff members in a particular module and what role they have, or on the other hand display a staff member page featuring his roles in the modules.

thanks for the help

Just have your three tables for Module, Staff and Role then add a relationship table to join all three together ie

Hope it helps

Mike

yes that makes sense thanks alot

the other problem i have is that i need to make a system whereby each user will have a number of tasks assigned to him for each module, depending on his role. For example the internal examiner would have to leave comments on the module's results as well as contribute comments on the module's action plan for the following year. I have added a table to store this review information for each module. What i would like is that when a user logs in he would be able to view his pending tasks and those which he has completed. how would i go about doing this in terms of db design? thanks

Personally I would just have a Tasks table like this

There are a number of changes you could make here though:

• I assumed the tasks and comments could be stored in the same table - I only did this cause I hate typing lots of code to create similar tables - YMMV.
• You may want or have a lookup table for allowed tasks per role.
• Would you want free text (item_txt) for task and then add some tasks automatically by the system or would you use task_id and use the lookup table.

As it stands the code to get a users tasks would just be:
But I don't understand why you want to show him the tasks he's done in with those he has to do.

Mike

I think the comments themselves should be separate from the tasks, i was planning to put them in a reviews table. These reviews also have to be archived every year (2006 reviews, 2005 reviews etc.) while the tasks remain the same and are pretty much fixed at the time of system design.

As regards to what i would like to show him, the user should be able to see all his pending tasks (sort of todo list and no need to see completed tasks). However for the role of the system administrator i would like to implement a sort of matrix view where he would see all the modules and the tasks for each module, then a symbol showing whether the task has been done or not.

A lookup table seems to be what i want in order to make this system, however I am still trying to work out the correct db design and system logic needed to be able to do these processes though, so any more help is welcome

right now this is what i have done, should this be good for my system or should i change some parts? thanks

http://jpgalea.com/design.jpg

Ignore that last Task table then and use:

I've no idea how your reviews are stored so I just used varchar(255) - I'll assume you have either a text type on your system or you'll point to an external file. We'll probably have to redo the RoleTask table so we can ensure tasks are done in the correct sequence but I'd need more info to do this.

Other thoughts:

• You might want to store how long a task should take (an SLA) in the task table then you could highlight tasks that haven't been completed in a reasonable time.
• Should all tasks and reviews have a module?
• It may be worthwhile showing all tasks that are outstanding on a module to all staff involved in that module as this tends to encourage people to do their bit.
• You could email users who have overdue tasks and perhaps send very overdue tasks to department heads.

Hopefully we're getting closer

Mike

yes i had thought of the sla thing and reminder functionality as well, but i thought i would add it as an additional function after completing the basics. i uploaded a document describing the system if you want to take a look at it, it should give you a better idea of the whole thing.

i am using mysql so varchar should be ideal

http://jpgalea.com/system.doc

thanks so much for your help!

Few comments on your design so far:

• It needs to store which type of tasks are for which type of role.
• Doesn't show what sequence tasks should be done - I believe the tasks need to be sequential looking at your spec.
• You're not storing an SLA for each task.
• You don't store when the task was assigned or completed.
• Why store username and then first and last names separately.
• Usually with field names it would be user_id rather than users_id.
• Why archive the old stuff? the database is quite compact so might be better to just screen out any info on modules that aren't for the current year. This way you won't have to write duplicate code to report on last years data (if required).

Mike

It needs to store which type of tasks are for which type of role.
I ADDED AN EXTRA TABLE WHICH SHOULD DO THIS

Doesn't show what sequence tasks should be done - I believe the tasks need to be sequential looking at your spec.
ANY IDEA HOW TO IMPLEMENT THIS?

You're not storing an SLA for each task.
I WILL LEAVE THIS FUNCTIONALITY OUT FOR NOW AS I DONT HAVE A CLEAR IDEA HOW TO MAKE IT WORK

You don't store when the task was assigned or completed.
I THINK THIS GOES WITH THE SEQUENCE OF TASKS, WHICH IM STILL NOT SURE HOW TO GO ABOUT YET, ANY IDEA?

Why store username and then first and last names separately.
USERNAME IS USED IN THE AUTHENTICATION SYSTEM, WILL BE DIFFERENT FROM THEIR FIRST AND LAST NAMES , EG. JOHN SMITH WOULD HAVE COJS AS USERNAME

Usually with field names it would be user_id rather than users_id.
AGREED, FIXED THAT

Why archive the old stuff? the database is quite compact so might be better to just screen out any info on modules that aren't for the current year. This way you won't have to write duplicate code to report on last years data
ARCHIVING IS A FEATURE REQUIRED BY THE CLIENT, AS THEY NEED TO CHECK WHAT HAS BEEN SAID IN THE PAST YEARS FOR REFERENCE OR AUDITING PURPOSES.

note: there are two types of archiving, that of the reviews themselves and that of modules which are not run anymore or staff who have left the university. the client wishes to retain this information, so i thought of just putting a field to flag whether they are current or archived. do you have a better idea for this?

thanks!

Could you just have a sequence number in the tasks for roles table. When someone completes a task then look up what the next sequence number is and the task associated with it, then create a task for the user who has that role for that module. As I understand it there is only one "flow" through the system but it may be worth having a field to group a whole series of tasks together so you can have different flows.

You might need some method of unfinishing a task as many people might just click finished to simply pass the buck ie if you've just been sent a task yet the previous task isn't done then click "not ready".

It wouldn't hurt to put the field in now as it will be less work to get this working. The field should just store the number of days allowed to complete this type of task.

All you have to do is look for tasks that aren't complete and where the start date for the task is more than X days ago where X is the SLA. For these cases you can just send out email reminders to those responsible. Perhaps you might want to run a similar query but after a further 7 days and send the, now very late, tasks to some department head etc.

If your method works then keep it. I was simply worried that you would store the details elsewhere and then have to write a similar set of reports to work on your archive data. It's usually best to keep the data all together and just ignore the older data by default - whether you do this with an archive flag or just using the fact that the current year has now changed is up to you.

Mike

i uploaded the latest version of the design, including most of your suggestions. regarding the archiving of the reviews by year the system will just check the year field in the modules table and treat the module and review as being from the past year if the modules.date field shows a date previous to the deadline for each year for example 30/06/200x. im not sure this will work but this is the only i got for how to do this.

any other observations welcome, thanks

The only comments I could come up with on your current design are:

• The primary key in the table modules_roles_users should be module_id, role_id and user_id. You should remove the id field.
  
• The primary key in the table tasks_modules_users should be task_id module_id and user_id. You should remove the id field.
  
• The primary key in the table roles_tasks should be role_id and task_id. You should remove the id field.
  
• With these tables you should make the name singular ie modules_roles_users becomes module_role_user. I personally find the names for your tables don't give me much idea of what data is in them or what they are for. YMMV.
  
• Certain fields are key works ie order and year.
  
• Does your review table need a user_id field?
  
• If a task is only ever performed by one role then I'd put the roles_tasks fields in to the tasks table and get rid of the roles_tasks table.
  
• I'd use date_completed rather than just a completed flag as this allows you to say how long tasks took to complete.
  
• I'd change the name of the time_allowed field to days_allowed so it's easy to see what units your counting in - ie days and not hours or weeks.
  
• How do you store in the database who is the administrator? I'd include a log table and perhaps a system params table for things like this.

My best advice would be to just produce an SQL script that will drop and recreate the tables (and perhaps add some test data) so you have something real to work with. In this way you'll feel you're getting somewhere and you can try some SQL to see how things work. This would also highlight where you have keywords for fields etc.

Mike

Also, depending upon the front end programming language, field names like year, code, and name are also often reserved words. While there are usually escape sequences to allow otherwise "illegal" naming, it is usually less confusing if you avoid their use altogether.

i.e. - Instead of Name, use User_Name, or Module_Code, rather than Code. It is more descriptive of the field, and it avoids potential conflict and/or confusion. In general, single-word field names are suspect.


What is the purpose of the Semester and Year fields in the Modules table? i.e. - can you have identical modules, except for the year and semester?

The primary key in the table modules_roles_users should be module_id, role_id and user_id. You should remove the id field.

The primary key in the table tasks_modules_users should be task_id module_id and user_id. You should remove the id field.

The primary key in the table roles_tasks should be role_id and task_id. You should remove the id field.

With these tables you should make the name singular ie modules_roles_users becomes module_role_user. I personally find the names for your tables don't give me much idea of what data is in them or what they are for. YMMV.

I am using CAKEPHP and it seems that it is better to use the id field when using this framework. Also that is why i used plural for the table names. These are cakephp conventions that allow you to rapidly prototype the application and play around with data as long as you keep to the conventions.

Certain fields are key works ie order and year.
yes good point i changed them

Does your review table need a user_id field?
i dont think this is necessary, the review table stores the module review and i cannot see why the user_id would be needed, were you thinking of some instance where this would be useful?

If a task is only ever performed by one role then I'd put the roles_tasks fields in to the tasks table and get rid of the roles_tasks table.
seems sensible however there is an instance where more than one role performs a particular task. this is if an internal examiner leaves the university, then the quality manager will need to take over his remaining tasks

I'd use date_completed rather than just a completed flag as this allows you to say how long tasks took to complete.
good point, however the client has told me that this feature is not needed

I'd change the name of the time_allowed field to days_allowed so it's easy to see what units your counting in - ie days and not hours or weeks.
yes definitely, i changed that

How do you store in the database who is the administrator? I'd include a log table and perhaps a system params table for things like this.
i dont really know how to do this, could you give an example of how i can make the tables for that?

thanks for all the advice!