Solved

How to set this database up?!!

Posted on 2014-12-13
4
110 Views
Last Modified: 2014-12-14
so i have this winform to make, its kind o like a contest or questions about general knowledge, arts, medicine, literature etc,... and it will calculate and sum the results o fa winning team, im using ms access DB, there are many way to design the db tables and so...here is the thing;
there are four departments...
there are 7 fields or areas of knowledge (history, arts, medicine,general questions, sports, etc.....)
each area or field, has 8 questions, and in turn each question is graded from (5,10,20,50,100,200,500,1000) and im not worried about primary keys...

the problem:
each of those depts, gets a turn in choosing a grade question (dept-1 chooses history question grade 5) now, if the answer is correct, they gain 5 grades, and that question cannot be used again

now im using drop downs and listboxes, but regardless, how do i design the DB in a way where it will disdable or hide the used questions????

thanks
0
Comment
Question by:ahashash
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40498448
and im not worried about primary keys...
But you need to be.  Every table needs to have a primary key even if it is an autonumber.  Without primary keys, you can't define relationships and enforce referential integrity and that is the glue that holds everything together.
If you are working in Access you are not using WinForms.  Access forms are unique to Access and different from WinForms although they have similar features.

Are you saying that the whole database only contains 56 questions?  Hardly seems worthwhile.

You need a table that defines field of knowledge.  A table that defines grades.   And a table that defines questions.

tblField
FieldID (autonumber, PK)
FieldName

tblGrade
GradeID (autonumber, PK)
GradeName
GradeValue

tblQuestion
QuestionID (Autonumber, PK)
FieldID (FK to tblField)
GradeID (Fk to tblGrade)
QuestionText
ValidAnswer

That defines the questions according to your description.  Now you have to use them and how that happens isn't clear.  You have something called department but after that it gets a little fuzzy.

tblDept
DeptID (autonumber, PK)
DeptName

tblDeptQuestion
DeptQuestionID (autonumber, PK)
QuestionID (FK tblQuestion)
ResponseValue

you would control the combos by joining to tblDeptQuestion and ignoring any question that has already been added for the department.

I know that what I described is an over simplification but start with that and we'll move on as you refine your spec.
0
 

Author Comment

by:ahashash
ID: 40498449
sounds great, will start on that
0
 

Author Comment

by:ahashash
ID: 40498451
the depts actually are considered as teams, 4 depts are four teams(3 people representing a dept) and they take turns in answering one question of their choice, like for example one team chooses arts, question grade 10, if they get it right, they receive 10 marks, but that question will not or should not be chosen/shown/available again because it has been answered
0
 
LVL 40

Expert Comment

by:als315
ID: 40498594
You can add contests table:
tblContest
ContestID (autonumber, PK)
ContestName

and add to table tblDeptQuestion contest as FK
Build complex index including QuestionID and ContestID and mark it as unique. In this case you will not be able to select one question in one contest twice. You can also add some logic to your form and remove already selected questions
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question