one table or three tables, which would scale/work better??

[tlyczko]

I have an audits database, with several tables,

tblAudits -- overall information about each audit, like who is doing
the audit, the date, where, etc.
AuditID is the primary key in this table.

tblProgramAudits -- unique information about program audits
tblRecordAudits -- unique information about a different kind of audit
tblMSCAudits -- unique info about yet another kind of audit
AuditID is a foreign key in all these three tables.

The above three tables have additional fields specific to the kind of
audit, so they have to be separate tables.

tblResponses to store audit responses for each kind of audit

ResponseID PK
AuditID FK
ProgramAuditID FK
RecordAuditID FK
QuestionID (comes from tblQuestions)
ResponseText (yes, no, n/a)
MSCProgramAuditID FK
MSCRecordAuditID FK
QuestionType -- program, record, MSC audit question?? -- probably don't
need to store this because a separate append query will create the
necessary response records ahead of time, end user only has to enter
the response text.

Is it better to use just one tblResponses to store all the audit
responses, or should I have separate response tables for each kind of
audit, such as tblProgramAuditResponses, tblRecordAuditResponses,
tblMSCAuditsResponses???

for example tblRecordAuditResponses would have

RecordAuditResponseID PK
AuditID FK
RecordAuditID FK
QuestionID
ResponseText
(don't need to store the QuestionType)

Would the DB scale better and work better with queries etc. with just
one tblResponses or should I have separate responses tables?? (It would
be more work to set up queries relative to three different response
tables, of course.)

I expect max 1-2 users editing and 3-5 users reading at the most, but
the response tables will fill up FAST -- 60-80 responses per program
audit, several hundred responses per record audit.

This will be a split DB, front end on end user's Citrix desktop,
backend on another server in the same room/LAN with the Citrix servers,
it may be necessary some day to port to SQL server, but for now
Access/Jet will work.

Also, for the tblResponse's foreign keys (e.g. ProgramAuditID), is it
better to have the table and forms default those values to Null or to 0
(zero)??? AuditID will always be in each row of tblResponses, but only
one of the other kinds of audit ID fields will have a value.

Another reason I ask about all this is that everything I learn with
this DB I will apply to creating a whole other DB with the same kinds
of usage patterns but completely different data.

Thank you, Tom

 

[tina]

i'm wondering why you need to tie tblResponses to the three audit tables at
all. you've linked tblResponses to tblAudits, and each table about a
specific audit *type* is also linked to tblAudits. and you only mention one
tblQuestions, so either all questions are general to all audits, or you're
already linked each question to a specific type of audit.

if you'd care to list each table with all its' fields AND how it is related
to each foreign key's table (one to many, one to one, etc), we can review
and perhaps suggest tweaks that will improve the tables/relationships
structure.

hth

 

[tlyczko]

Hello Tina,

I'll post table/field listings later tonight.

Programs to Audits is 1:1.
Records to Audits is 1:M.

Each program or record audit is 1:M to Responses.

Questions are specific to what kind of audit it is, program, record or
MSC.

You are right, I probably don't absolutely have to have AuditID as a
foreign key in tblResponses, for now it is not a problem and helps keep
track of things.

Thank you, Tom

 

[tlyczko]

Table/field listings as requested above:

tblAudits
AuditID PK
Audit Date
Auditor Name
Audit Location
Audit Type
Program ID -- program being audited, all locations are in programs
Audit Comments/Info
etc. etc.

tblProgramAudits
ProgramAudit ID PK
Audit ID FK
Program Audit Comments/Info

tblRecordAudits
RecordAudit ID PK
Audit ID FK
Staff ID
Consumer ID

tblMSCProgramAudits and tblMSCRecordAudits are set up the same way as
the other two above tables.

tblResponses
Response ID PK
Audit ID FK
ProgramAudit ID FK
RecordAudit ID FK
MSCRecordAudit ID FK
MSCProgramAudit ID FK
Question ID
Response -- Y, N, N/A

Thank you, Tom

 

[tina]

tblMSCProgramAudits and tblMSCRecordAudits are set up the same way as

the other two above tables.

you just lost me. your first post listed three tables linked to tblAudits:
tblProgramAudits, tblRecordAudits, and tblMSCAudits. this last post listed
the fields (all of them, i hope) in tblProgramAudits and tblRecordAudits.
how did we get from one remaining table - tblMSCAudits - to the two
additional tables you mention above?

i notice that you have an AuditType field in tblAudits; are "program" and
"record" the names of two *types* of audits?

based on your last two posts, tblProgramAudits is a subclass of tblAudits,
while tblRecordAudits is the "many" side of a one-to-many relationship with
tblAudits. in both cases, i can't see any reason to link these two tables to
tblResponses. as i said before, it seems that tblResponses should be linked
to tblAudits, and direct links to the "child" tables of tblAudits is
unnecessary.

hth

 

[tlyczko]

Hello Tina,

Thank you for replying.

Yes, Program and Record are two kinds of audits, each has its own
separate subset of different questions for the various types of audits,
explained here, not to mention that the questions chosen depend on what
program is being audited, and various other factors I'm not explaining
here.

AuditType in tblAudit does not mean Program or Record, it means
something completely different (full audit, partial audit, medical
audit, fire safety audit, etc.). It is more a description, but the way
staff here talk about audits, they use "type" to mean "description."

tblResponses is the collection of all answers to either program or
record audit questions.

For program audits, tblResponses will have 1 set of responses, for
record audits tblResponses will have several sets of response.

Meanwhile I will ponder your comments, but I'm going to maintain things
the way they are because I have to keep it separate that each audit has
two parts -- one program audit and several record audits. Some audits
don't have record audits either.

I was only asking about the best way to store the data in tblResponses
-- one table for all audit responses or one table of responses per
audit, be it a program or record audit or any other kind of audit.

Thank you, Tom

 

[tina]

okay. i'm just really not comfortable with putting different kinds of audits
in separate tables, without a *very* good reason. assuming that you posted
the complete fields list for tblProgramAudits and tblRecordAudits - and
assuming that the number of fields in tblMSCAudits is similar - i think
you'd be better off to put them into one table, with the addition of a field
to designate what kind of audit each record is. to have only a few fields
that are populated in only a portion of the records is not a big deal, and
having one table for these audits (still linked to tblAudits) would make it
easier and more efficient to link with tblResponses.

food for thought, anyway.

hth

 

[tlyczko]

Hello Tina,

Thank you for replying again. I think we could go round and round about
this.
I am understanding better what you're saying here.

Another way of looking at this is that an audit is a survey event
occurring on some particular day at some location, done by some person
about 1 particular location, and about several people associated with
that particular location, and program is only a survey about that
location, and record is one survey about each one of those several
people associated with that location.

It's complicated so I will really consider what you said.

Separate tables make it easier to think about the different types of
audits/surveys of either locations or people, but one table as you
suggested may be more efficient.

Thank you, Tom

 

[tlyczko]

Hi Tina, I tried what you suggested.
It is more efficient, but the problem I encountered is that I can't
have two subforms on the same main form referencing the same audits
table that you recommended using, data in previously created rows gets
overwritten/changed, etc.
I can't get the subforms to only create/edit *new* rows, so I'm going
back to using separate tables so I can have the data entry for each
kind of audit (program or record) on the same form.
It's not as well normalized as it could be, but something is better
than nothing.
I posted to the formscoding group to ask about the subform issue.
Thank you for all your help so far, I've noticed you know quite a lot
about Access.
Thank you, Tom

 

[tina]

you're welcome. and good luck with the issue in your new thread; perhaps
someone will offer a solution that will work with a single audit table.