How to design table of working-tasks in a meaningful way?

[ICBSprod]

Hi!


This is embarassing. I have earlier worked with complex databases, mainly in access 2000. But this was some years ago, and now it seems that i am stuck with some of the most simplest tasks. I would appreciate feedback from more than one of you on this one. (I will later on post a report issue regarding this same database)
I am going to finish a database I started building some years ago, (it has been "sleeping" in about 9 years) that are going to handle my working tasks: What I do at work, when I do it and how much time I use on each task etc. For the information, I work in the music school, and as organist in the church. It is a simple database: It is not going to handle payment, calculations of extra payment after 17.00, or at night etc. It’s just an advanced time-table.

This is issue is about how to organise or design my working tasks.
I have this:

Table: "WorkingTasks"

WorkingTaskID	WorkingTaskMainCategory	WorkingTaskSubCategory
AutoNumber	Music Scool	Meetings
	Music Scool	Rehearsing
	Music Scool	Preparation lessons
	Music Scool	Teaching
	Organist	Adm
	Organist	Office
	Service	Planning
	Service	Preparation
	Etc.

I need this information for 2 purposes.
1: As a description of my working tasks, with or without the «SubCategory»
2: I want a total sum of my working hours, and I want the sum of working hours corresponding to «Music Scool» and «Church» separately. This can here be handled by using «Like Music School» and «Not Like Music School».

There are however two other way to design this:
ONE: Table "WorkingTasks"

WorkingTaskID	WorkingTaskMainCategory 2 alternatives in drop down box from the / inside Table («Music Scool» and «Church»)	WorkingTaskSubCategory
AutoNumber	Music Scool	Meetings
	Music Scool	Preparation lessons
	Music Scool	Teaching
	Church	Office
	Church	Service - Planning
	Church	Service - Preparation
	Etc.

I originally pictured in my head that these records should only contain «one word», short and consise for each field, but '
this way there will be more words/explanation in the «WorkingTaskSubCategory»: «Church»; «Service – Planning»
Alternatively I can add a new field: «WorkingTaskSubSubCategory» like this: «Church»; «Service» ; «Planning».

TWO:
The second option is to do it the «proper» way:
One table handling the WorkingTaskMainCategory, like this:

Table «WorkingTaskMainCategory» (This will never have more than 2 records)

WorkingTaskID	WorkingTaskMainCategory
CH	Church
MS	Music School

And one connected table like this:
Table: «WorkingTaskSubCategory» (This will have approximately between 50 and 60 active records in actual use (the working tasks will change as time goes by)).

WorkingTaskSubCategoryID	WorkingTaskID From Table «WorkingTaskMainCategory»	WorkingTaskSubCategory	(Alternatively WorkingTaskSubSubCategory )
AutoNumber	CH	Service - planning	(Planning)

What is the most appropriate way to go here?
I would appreciate several comments on this

[rpeare]

I'd have a construction like this:

tblWorkGroup
WG_ID  WG_Desc ---> other work group related fields
1      Church 
2      Church - Services
3      School

tblWorkSubGroup
WSG_ID  WG_ID  WSG_Desc ----> other subgroup related fields
1       1      Administration
2       1      Office
3       2      Planning
4       2      Preparation
5       3      Meeting
6       3      Rehearsal
7       3      Lesson Preparation
8       3      Teaching

Then store the PK of each table in your 'work' table rather than a text value. You can do any sorting/grouping you want on the actual description fields simply by linking in the table that holds the description and adding the description field.

Then you'd want something like this to actually store your data of your activity:

tblWorkDays
WD_ID  WD_Date ----> other specific workday related fields
1      1/1/2014
2      1/2/2014
3      1/4/2014

tblWorkDetails
WDD_ID  WD_ID  WDD_Start  WDD_END   WG_ID  WSG_ID
1       1      9:00 AM    9:30 AM   1      1
2       1      9:30 AM    10:00 AM  1      2 
3       1      10:00 AM   11:00 AM  3      6

This would allow you, in your data entry, to limit your subgroup to the items that are directly related to your meta group (office, church, church - services).