Simple Job Logging Database

[garg]

Hello,

I want to create a database driven website as a learning project. Please help me out with this.

I have a few machines on which I have to log a couple of tasks. Tasks such as harddisk testing, defrags, back up, deleting temp files etc. I also want to log who does the tasks, which machines had the tasks done, what task was done.

I want to be able to add more users, machines, and tasks in the future.

Example output would be:

User | date | Machine | Task 1 | Task 2 | .... | Task N
bob | mmyydd | machine1 | done | not done | .... | done

How would you suggest I make this simple database?

Would the following work?

Users Table
-------------
user_id
username
password
firstname
lastname

Machines Table
------------------
machine_id
machinename

Tasks table
------------------
task_id
task

Jobs Table
------------
job_id
date
user_id
machine_id
tasks

Job-Task Table
-----------------
job-task_id
job_id
task_id

User Table would store the users.
Machine Table would store the machines/computers.
Tasks Table would store the available tasks
Jobs table would store the date, user id, machine id and a reference to a job_Task item
Job-Task Table would match job id with task id so it would be like:

Job Task Table output
-------------------------
id | job_id | task_id
---------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 4
4 | 2 | 1
5 | 2 | 3

Thanks!

[trudge]

Your design seems to be well on the way to what you want. In your 'Job Task Table output' you might want to replace the task ID with what the actual task is, unless you can remember what a task_id of 1 (or 3, or 7, ...) is etc.

Remember that your design and relations are driven by the kinds of queries you anticipate making. Populate your tables with some dummy data and perform some queries that cover what you will eventually want. Check the results for correctness (neither false positives nor false negatives). If you find that you can't perform a certain query based on the current design, you may have to change your design to suit. Also, it's generally not a good idea to store any values that can be derived or computed from existing data.