[Solved] Proper Table Structure

[tobypsl]

I am creating a planning DB and have to decide how to store Tasks. I want to be able to list all tasks by, for example, date in a report. But tasks also have subtasks that will also appear in the report.

So I guess the obvious is to have a subtasks table.

But then I am wondering what type of report would list parent items and child items in the same list?

(Some tasks wont have subtasks.)

I did think about having tasks and subtasks in the same table - but I'm guessing this would be classed as bad design.

This DB is just for personal use.

Any thoughts appreciated.

[Villeroy]

Read about database normalization.
Think in lists, e.g. a lists of clients, a list of invoices, a list of articles where each invoice must have one client and each invoice can have more than one article which requires another list of invoiced articles.
Each row (record) in a list (table) represents no more than one distinct item.
Each column (field) represents one property of all items.

You have a list of tasks with an auto-id and a list of subtasks, each with their respective set of properties in fields. Now the question is if one subtask belongs to one specific task (one-to-one relation), if one subtask may belong to many tasks (one-to-many relation) or if any of your subtasks may be related to any of your tasks (many-to-many with a third table mapping tasks to subtasks).

[tobypsl]

OK sounds sensible. I've worked out a structure that actually has a hierarchy that goes from 5yr, 1yr, Qtr, Job, Task. Which is actually quite easy to work with and doesn't repeat data in tables.

Would like to represent in report format with tasks nested inside Jobs and jobs nested inside Qtr etc. ie.:

Qtr 1
-Job 1
--Task 1
--Task 2
--Task 3
-Job 2
--Task 1
--Task 2
-Job 3
--Task 1
--Task 2
--Task 3

Qtr 2
-Job 1
--Task 1
--Task 2
-Job 2
--Task 1
--Task 2
-Job 3
--Task 1
--Task 2
--Task 3

etc.

But will come back to that when at report stage.

[MTP]

If each task is unique to each job, then you'll just have a TASKS table with columns [TaskID, JobID, TaskName]. If each task may be part of multiple jobs, then you'll need to set up a join table as Villeroy alluded to. Here is an example set of tables with TABLE NAME in all caps and the column names for each table under the table name:

JOBS
JobID
JobName

TASKS
TaskID
TaskName

JOB_DETAILS (this is the join table)
DetailID
JobID
TaskID

For the Jobs-Quarters relationship, I'm guessing you'll have a start date and end date for jobs. Then you'll write a query to show which quarters the jobs were active in, and use that query as the basis for your report.

Learning the SQL to set that query up will be a bit of a learning curve.

Once you have your query set up, showing subgroups is pretty standard in reporting. You'll see how to do it if you go through a Base report tutorial like [Tutorial] Report Builder First Steps.

[tobypsl]

I'll check that tutorial out when I get to reports thanks MTP.

As for the quarters - they will have a description and also a review field - both of which will be text. I need to think about whether I want the jobs to be calculated or assigned to a quarter. Because the DB is a planning and task management tool it is valuable being able to assign jobs to quarters from a planning perspective. Although I understand that from a design point of view there is no need.

Also although right now I don't intend for tasks to be part of more than one job - it could be a useful idea, so I need to consider.

I'll probably design what I think I need, test that for a month and then redesign. Again this probably doesn't sound ideal but because I want to use this as a tool to improve my planning, time management and forecasting I'm not creating something for the job I'm already doing - but to help evolve what I already do into something new.

Anyway thanks for tips.

[Villeroy]

You will query some record set from the jobs, tasks and job details and it will look like this:

Code: Select all

Qtr 1	Job 1	Task 1
Qtr 1	Job 1	Task 2
Qtr 1	Job 1	Task 3
Qtr 1	Job 2	Task 1
Qtr 1	Job 2	Task 2
Qtr 1	Job 3	Task 1
Qtr 1	Job 3	Task 2
Qtr 1	Job 3	Task 3
Qtr 2	Job 1	Task 1
Qtr 2	Job 1	Task 2
Qtr 2	Job 2	Task 1
Qtr 2	Job 2	Task 2
Qtr 2	Job 3	Task 1
Qtr 2	Job 3	Task 2
Qtr 2	Job 3	Task 3

A grouped record can generate the layout you've outlined.

[tobypsl]

Thanks Villeroy. I've created a query and I can setup a report with grouping similar to how you suggested. I've attached a screenshot of the table relationships.

The issue I have is that in the query if a Job has no tasks associated with it it simply doesn't appear in the query. Which means the job is then missing from the report.

Because 'Jobs' will be assigned well in advance of 'Tasks' (and at some point I'll also be adding in a time forecast for jobs and tasks) they are an important feature of planning - whether they have tasks associated yet or not.

I'm guessing there may be a different way of handling the query and / or structuring the report such that empty Jobs are still reported.

Any ideas appreciated.

[MTP]

In your query, when you join the JOBS and TASKS tables, use a LEFT JOIN instead of an INNER JOIN. This will make all jobs appear; a job that has no tasks will appear as a single line with a NULL value in the Task column(s).

Wikipedia has a nice article with examples of the different kinds of SQL joins: https://en.wikipedia.org/wiki/Join_(SQL)

[tobypsl]

MTP, that looks promising thanks. Because the article presents a simple structure with no criteria the structure of my SQL will be a little different and I'm not sure whereabouts the LEFT INNER JOIN statement goes. I've tried the following, just tacking the join to he end of the statement:

Code: Select all

SELECT "tblYear"."YearDescription", "tblQuarters"."QtrDescription", "tblQtrObjectives"."Objective", "tblJobs"."JobID", "tblJobs"."Description", "tblTasks"."Title" 
FROM "tblQuarters", "tblYear", "tblQtrObjectives", "tblJobs", "tblTasks" 
WHERE "tblQuarters"."YearID" = "tblYear"."YearID" AND "tblQtrObjectives"."QtrID" = "tblQuarters"."QtrID" AND "tblJobs"."ObjectiveID" = "tblQtrObjectives"."ObjectiveID" AND "tblTasks"."JobID" = "tblJobs"."JobID"
LEFT OUTER JOIN "tblTasks" ON "tblTasks"."JobID" = "tblJobs"."JobID"

But it generates a syntax error - can I generate the query you suggest using the wizard?

Also the table relationships are predefined outside the query in the relationships window. Will a SQL statement redefine the join for the purposes of the query?

[Villeroy]

Possible clauses of a SELECT statement in correct order:

Code: Select all

SELECT ...
FROM ... JOIN ... JOIN ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

Code: Select all

SELECT "tblYear"."YearDescription", "tblQuarters"."QtrDescription", "tblQtrObjectives"."Objective", "tblJobs"."JobID", "tblJobs"."Description", "tblTasks"."Title"
FROM "tblQuarters", "tblYear", "tblQtrObjectives" LEFT OUTER JOIN "tblTasks" ON "tblTasks"."JobID" = "tblJobs"."JobID"
WHERE "tblQuarters"."YearID" = "tblYear"."YearID" AND "tblQtrObjectives"."QtrID" = "tblQuarters"."QtrID" AND "tblJobs"."ObjectiveID" = "tblQtrObjectives"."ObjectiveID"

... FROM A,B WHERE A.X=B.Y
is equivalent to
... FROM A JOIN B ON A.X=B.Y
so the following may work as well:

Code: Select all

SELECT "tblYear"."YearDescription", "tblQuarters"."QtrDescription", "tblQtrObjectives"."Objective", "tblJobs"."JobID", "tblJobs"."Description", "tblTasks"."Title"
FROM "tblQuarters" JOIN "tblYear" ON "tblQuarters"."YearID" = "tblYear"."YearID"
  JOIN "tblQtrObjectives" ON "tblQtrObjectives"."QtrID" = "tblQuarters"."QtrID"
  JOIN "tblJobs" ON "tblJobs"."ObjectiveID" = "tblQtrObjectives"."ObjectiveID"
  LEFT OUTER JOIN "tblTasks" ON "tblTasks"."JobID" = "tblJobs"."JobID"

[tobypsl]

Thanks Villeroy. Interestingly if I run the first code and execute the query OpenOffice crashes. If I run the second code the query executes and it does indeed display the full results I'm looking for. I've substituted the new query into a report and can just tweak the layout now. Thanks again.