should I give each job result its own table?

[malcolmk]

Hi, I am planning a test request, process and report system. It goes like this, a customer requests a set of tests to be carried out on a particular item; the tests may be medical or mechanical or anything else. The point is each request is given a job number and each job number may contain a request for many tests.
Instead of a table containing many entries of job numbers
ie.
job 001 test1 result
job 001 test2 result
job 001 test3 result
job 001 test4 result
job 001 test5 result
job 001 test6 result
......etc
job 002 test1 result
job 002 test2 result
....etc

would I be better of generating a new results table for each job.
ie.
Table job001
=============
test1 result
test2 reult
etc

I am thinking that each job having its own table is maybe easier to archive or delete a complete job record etc
Also possible to email out a link to a particular job table if required by the customer!

Thanks for looking.

[nico5038]

Guess I need to point you first to our article about normalization:

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

In this case I would create a tblJob with the JobNumber and e.g. the customer.
Next a tblTest can be created with the JobNumber (for the link) and the testnumber.

Finally, when there are "fixed" sets of tests you could create a tblTestTemplate holding one or more templates to insert a set of testrecords into the tblTest.

Getting the idea ?

Nic;o)

[malcolmk]

Hi, I already have my tables set up, I was just wondering if it may be a good idea to dump individual jobs to their own tables and not keep them all in one large results table. I suppose I could just generate a report and email that or a link to it to the customer.
My main reason for the question is because I am planning to move this project from access to a standalone vb app.

Tables I have are.

TEST SYSTEM STRUCTURE
=============== =============

TESTS
======
testid as string ' eg \001\ or \bno\
testname as string ' name or abbreviation of test
category as string ' eg \001\ or bno\ same id system as testid select from linked list
description as string
upper as string ' measure result or upper bound
lower as string ' mearure result or lower bound
normal as string ' typical result
units as string ' unit of measure

CATEGORY
===========
catid as string ' same id format \??\
category as string ' name of category
description as string ' description

PROFILES
=========
profid as string ' same id format \??\
teststring as string ' contains id's of all included tests in profile

CLIENT
==========
info on who requested the test

client as string ' could be department name, company name or individual
costcode as string ' id of customer

CLIENTDETAIL
==============

clientid as string ' some identifier
intextn as boolean ' internal or external customer
title as string ' mr, mrs, miss, dept
fname as string
sname as string
company as string
dept as string
position as string
address1 as string
address2 as string
town as string
county as string
postcode as string
tel as string
email as string

JOB
=====
detail of the requested job

client ' id of client, lookup client id
requestedby as string ' who signed or authorised request
jobid as string ' id of this job for tracking
requestdate as date ' date job requested
status as string ' waiting, in progress, complete, attention required
shortdescrip as string ' short note on job
jobteststring as string ' contains job codes to be run
jobstartdate as date
jobenddate as date

TESTED
=========
completed jobs record

jobnumber as string ' id the requested job
technician as string ' who is running job
date as date
testname1 as string
upperres as string
lowerres as string
actualres as string
comment as string

USERS
==========
system users

userid as string
password as string
username as string

System works fine in access as is but ofcourse the tested table just gets bigger and bigger; as the results may need to be kept for 5-6 years I think I really should export data at some point!

So do you think just generate reports and maybe export data for jobs between certain dates every 6 month or so or dump results straight into dedicated table?
Thanks.

[NeoPa]

The answer is an unequivocal "No" Malcolm.

Moving data to a separate database may be called for if space is tight (although even that is far less often than many seem to think), but I've not heard of any scenario where a separate table, or set of tables, makes good sense. If you were to read the linked article I suspect that you would come to that same conclusion yourself.

By the way, redoing your tables now, if they do not conform to the basics of normalisation, will be worth the effort. It may be some effort, but I can almost guarantee that the structure will trip you up at some point if you don't take this opportunity now.

[nico5038]

Basically you "create" a separate Job -> test table when filtering the JOINed tables for one specific JobID.

The database will create such a resultset rather fast when the JobID is indexed.

Nic;o)