Correct Relationships

[goslincm]

Good morning, struggle to establish proper relationships.

tbl_survey

surveyID pk
surveyName

tbl_questions
questionID pk
surveyid fk
question

tbl_answers

answerID
surveyID
questionID
answer

Question: in my tbl_answers what should my primary key be? combination of the three ID fields?

 

[dhookom]

I almost always create a single field primary key based on an autonumber. Creating a unique index isn't the same as a primary key. IOW I would keep both the JunctionID (but change the naming) and the multiple field unique index.

"tblJunction" might be a decent name in a cyber-exercise but it tells us nothing about its usage. I would probably name the table tblPatronBookTransaction or similar.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

There are two schools of thought on this, gos. This is correct:

TblPatrons
PatronID Pk
Name
Phone#

TblBooks
BookID Pk
Title
Author

TblJunction
PatronID Pk
BookID Pk

Note that I have removed the patron key from the books table, as PHV points out should be done. This is also correct:

tblPatrons
PatronID pk
Name
PhoneNo.

tblBooks
Book ID pk
Title
Author

tblJunction
JunctionID pk
BookID
PatronID

Each has its own challenges in implementation, in particular in the way they handle duplicate records.

And personally, I wouldn't call it tblPatronBookTransaction, I'd call it tblLoans, since I'm the simple-minded sort.

To sum up relational database theory, really all you need to do is break everything into one to many relationships. If you have a many to many relationship, then you need to create another table that is in one to many relationship with each, and use that to connect them. Everything else is just detail.

So, now that you can build the database of the Library of Congress with complete confidence, suppose you revisit your problem and refactor the design?

HTH

Bob

 

[goslincm]

Thanks working on restructuring my survey tables now. Are there some type of online exercises and individual such as myself can refer to to "practice" table structuring? I could use some exercises to do over and over if that is out here.

 

[dhookom]

You might want to consider finding a copy of Database Design for Mere Mortals by Michael Hernandez.

http://www.bookpool.com/ss?qs=michael+hernandez

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Thanks Duane, I will find a copy to purchase right away.

So now my table structures look like this:

tblSurveys
surveyid-pk
name

tblQuestions
surveyid-pk
question
date

tblAnswers
answersid-pk
answer

tblResponses
responsesid-pk
answersid
respondersid

tblResponders
respondersid-pk
name

Hopefully this is better but feel free to be honest as painful as it can be its the only way I'm going to learn this.

 

[dhookom]

tblSurveys
surveyid-pk
name [red]never name a field name since name is a property of all objects[/red]

tblQuestions
surveyid-pk [red]shouldn't this be QuestionID[/red]
question
date [red]never name a field date since date is a function. Consider being more explicit with some other name. Why would a question have a date?[/red]

tblAnswers [red]Are these possible answers to select from? How are they associated with any question?[/red]
answersid-pk
answer

tblResponses [red]How is this related to any question?[/red]
responsesid-pk
answersid
respondersid

tblResponders
respondersid-pk
name [red]never name a field name since name is a property of all objects[/red]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BobRodes]

I agree with all of Duane's red markups. I should have looked at the field names before I pronounced the tables "correct", although they are correct in terms of relationships.

Now, more stuff to add to Duane's. tblQuestions does indeed need a surveyid, but it's not a pk, it's an fk. Suppose you write fk next to all the fields that are foreign keys.

So, I gave you the relations, and you're on the right track. Duane, I believe tblResponses is done correctly. It's related to questions indirectly via answers. Since answers only have one question related to them, this works. Also, attempting to relate directly to questions still begs the question of which answer was responded to, so it looks right to me to use answers as the FK, not questions. I'm thinking a user makes an answer, a record gets added to the answers table, with which answer was selected. If you dont' find the record in the answers table, that answer wasn't selected, otherwise it was.

Gos, you don't have tblAnswers related to anything at all. How would you relate it to questions? Also, in questions table, there needs to be a questionid pk, and also an fk to relate to the survey table.

By the way, I'm assuming that an answer goes to one question which goes to one survey. If multiple surveys can have the same question, that gets more complicated.

Bob

 

[goslincm]

Sorry I wasn't getting specific with my naming conventions out here, I was more concerned about getting the concepts at this point. Thanks for the notations in red.

tblSurvey
surveyid-pk
surveytitle
versionno

tblQuestions
questionid-pk
question
surveyid-fk

tblAnswers
answerid-pk
answer
questionid-fk

tblResponses
responseid-pk
answerid-fk
responderid-fk

tblResponders
responderid-pk
lname
fname

 

[lespaul]

gos,
Can you answer some of the questions above? It's hard to determine if the relationships are actually correct without knowing a little more....can you give us a sample question and it's answers? Is there only ONE survey with a set of questions? Is there a chance (EVER) that there will be more surveys?
Thanks,
Leslie

 

[goslincm]

Leslie, this thread has gotten very "complicated". I started out trying to get familar with the AT YOUR SURVEY that someone referred me to. From there I started with the questions.

What I have to do is work with an existing unnormalized table, which is capturing the answers (all yes/no) to a set of questions that must be completed annually. What needs to be in place at this point is a letter that goes out to each responder, with some text explaining why they are or are not qualified for a particular service.

I cannot easily do the letter due to the structure of the existing table. So, I stepped back and am trying to create a proper table stucture for this that will assist me in the long run (hopefully not too long) in creating the report that needs to go out.

I cannot see more survey's going out, but I can see additions or deletions to the questions being asked based on statute changes.

Sample Question: Has the unit complied wtih GASB 34 and other generally accepted accounting principles (GAAP)? Yes/NO

Eventual Goal: Report that will kick out something like:

Based on the receipt of your annual APR Survey, you are not qualified because of answering no to the following questions:

-Has the unit complied with GASB34 and other generally accepted accounting principles.

 

[dhookom]

Why can't you just start with AYS tables as they are and maybe add a field here and there to meet additional requirements. The tables are fairly well normalized and most of the people that contact me regarding AYS use it "straight out of the box".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

this thread has gotten very "complicated"

Oh, I know....I've been lurking all along trying to see how it worked out!

This additional information is definitely helpful!

1. You have a set of questions that need to be asked on an annual basis.
2. The set of questions can change from year to year...so you will need to know which questions were asked each year.
3. All the questions are answered either Yes or No.

does that sum up the data requirements? If so, I would structure it like so:
[tt]
Responders
ResponderID (PK)
FirstName
LastName
(all other information about the responder)

Surveys (enter a new survey each year?)
SurveyID (PK)
SurveyTitle
Version

Questions
QuestionID (PK)
QuestionText
(Optional: ReportText)

SurveyQuestions (this is the table that determines which questions were asked which year)
SurveyID (FK)}
QuestionID (FK)} composite PK (highlight both in the table design view and then press the key)

ResponderSurveys
ResponderSurveyID (PK)
ResponderID (FK)
SurveyID (FK)

Responses
ResponseID (PK)
ResponderSurveyID (FK)
Response (Yes/No)[/tt]

By adding the ReportText field to the question table you can then use a slightly different wording that works better for reporting purposes:

[tt]Based on the receipt of your annual APR Survey, you are not qualified due to the following:

-The unit has not complied with GASB34 and other generally accepted accounting principles.[/tt]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[goslincm]

Leslie, thanks for the information. Makes sense to add the reporttext field to the questions table.

Are we not using the surveyid as a fk in that table due to the creation of the SurveyQuestions table?

Also, as this sets the stage for part one of my needs (normalized table structure), what would my next step be: creating all the necessary forms and subforms (what order do those go) OR to move (somehow) the information from the existing table over into these?

Duane, obviously its not that there is anything wrong with your AT YOUR SURVEY, I love it, I started with that but as everyone out here can see, this whole thing is a learning process that I MUST get through and I think going backwards like this from scratch is the best way for me to get it.

 

[lespaul]

Are we not using the surveyid as a fk in that table due to the creation of the SurveyQuestions table?

That's one reason, the other is that if you add SurveyID as a FK to the Questions table then you can only use that question on that survey. Your requirements are that the questions can change from survey to survey. If you keep SurveyID in the Question table then you would need to add every question every year! So your question:

Has the unit complied wtih GASB 34 and other generally accepted accounting principles (GAAP)?

Would be in there multiple times for each year you ask that question. With the SurveyQuestion table you have a single list of questions that can be used on many different surveys (each year is can be a new survey).

Does that make sense?

Leslie

 

[goslincm]

Alright, yes that makes sense.

 

[goslincm]

Also, as this sets the stage for part one of my needs (normalized table structure), what would my next step be: creating all the necessary forms and subforms (what order do those go) OR to move (somehow) the information from the existing table over into these?


Not sure anyone wants to answer this one for me?

 

[lespaul]

I personally would move all the data over before starting the form design, that way I can check and make sure all the relationships are working correctly and enforced. Plus it's easier to see if the form works like you expected when there's real data there to see.

As for the forms, I would draw out on paper what I envision and work out any issues before i actually started designing any thing.

Leslie

 

[goslincm]

When moving the data over I'm dealing with one table which has all the questions in it, as well as responder information. I've not done this before. Am I creating an append query for this? And would I pull in the responder information part first and then the questions?

 

[goslincm]

Perhaps I should post how I believe these tables all relate:

Surveys1.RespondersSurveysM
Surveys1.SurveyQuestionsM
Questions1.SurveyQuestionsM
Responders1.RespondersSurveysM
RespondersSurveys1.ResponsesM

 

[lespaul]

Looks like the relationships are set up right. To transfer the data will depend on the current table structure...What I do is create a query that gets me all the data I need. Then convert it to an insert query.