T
tlyczko
Hello,
I would appreciate some feedback about my tables design.
I am creating a database for collecting data about audits in our
agency.
We audit services provided by programs and people who receive services.
I have the following tables:
Audits Overall -- Audit ID (PK) who did the audit, date, etc.
----- Audits Overall connects to:
Program Audit -- Program Audit ID (PK), where, other information about
this day's audit of the program (1:1 to Audits Overall)
Program Audit Responses -- Response ID (PK), Audit ID, Program Audit
ID, Question ID, ResponseText (1:1 to Audits Overall and Program Audit)
Program Audit Comments -- Comment ID (PK), Audit ID, Program Audit ID,
Question Topic, Program Topic Comment (1:M to Program Audit and Audits
Overall)
------ Audits Overall connects to:
People Audit -- People Audit ID, person name, other information about
this day's audit of this person (1:M to Audits Overall) This is enough
different informtion that it warrants its own table separate from
Program Audit.
People Audit Responses -- Response ID (PK), Audit ID, People Audit ID,
Question ID, ResponseText (1:1 to Audits Overall and Program Audit)
People Audit Comments -- Comment ID (PK), Audit ID, People Audit ID,
Question Topic, People Topic Comment (1:M to People Audit and Audits
Overall)
------
My question is whether or not I should use THIS table structure for
Audit Responses and Audit Comments so I have two tables instead of
four:
Program AND People Audit Responses -- Response ID (PK), Audit ID,
People Audit ID, Program Audit ID, Question ID, ResponseText (1:1 to
Audits Overall and Program Audit) -- only People Audit ID or People
Audit ID can have a value, the other field cannot have a value, this
helps keep rows unique.
Program AND People Audit Comments -- Comment ID (PK), Audit ID, People
Audit ID, Program Audit ID, Question Topic, People or Program Audit
Comment (1:M to People Audit and Audits Overall)-- only People Audit ID
or People Audit ID can have a value, the other field cannot have a
value, this helps keep rows unique.
Question IDs and Question Topics are _different_ for Program and People
Audits.
Thank you, Tom
I would appreciate some feedback about my tables design.
I am creating a database for collecting data about audits in our
agency.
We audit services provided by programs and people who receive services.
I have the following tables:
Audits Overall -- Audit ID (PK) who did the audit, date, etc.
----- Audits Overall connects to:
Program Audit -- Program Audit ID (PK), where, other information about
this day's audit of the program (1:1 to Audits Overall)
Program Audit Responses -- Response ID (PK), Audit ID, Program Audit
ID, Question ID, ResponseText (1:1 to Audits Overall and Program Audit)
Program Audit Comments -- Comment ID (PK), Audit ID, Program Audit ID,
Question Topic, Program Topic Comment (1:M to Program Audit and Audits
Overall)
------ Audits Overall connects to:
People Audit -- People Audit ID, person name, other information about
this day's audit of this person (1:M to Audits Overall) This is enough
different informtion that it warrants its own table separate from
Program Audit.
People Audit Responses -- Response ID (PK), Audit ID, People Audit ID,
Question ID, ResponseText (1:1 to Audits Overall and Program Audit)
People Audit Comments -- Comment ID (PK), Audit ID, People Audit ID,
Question Topic, People Topic Comment (1:M to People Audit and Audits
Overall)
------
My question is whether or not I should use THIS table structure for
Audit Responses and Audit Comments so I have two tables instead of
four:
Program AND People Audit Responses -- Response ID (PK), Audit ID,
People Audit ID, Program Audit ID, Question ID, ResponseText (1:1 to
Audits Overall and Program Audit) -- only People Audit ID or People
Audit ID can have a value, the other field cannot have a value, this
helps keep rows unique.
Program AND People Audit Comments -- Comment ID (PK), Audit ID, People
Audit ID, Program Audit ID, Question Topic, People or Program Audit
Comment (1:M to People Audit and Audits Overall)-- only People Audit ID
or People Audit ID can have a value, the other field cannot have a
value, this helps keep rows unique.
Question IDs and Question Topics are _different_ for Program and People
Audits.
Thank you, Tom