If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Question about reducing number of tables in a database
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
HELP! CANNOT CONNECT TO SQL SERVER | Glint | General Discussion | 19 | May 9th, 2005 02:47 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Combo Box (1st) Populating Text Box (2nd) Field | AccessRookie | Using Forms | 1 | April 6th, 2005 11:37 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |