Solved

How to set this database up?!!

Posted on 2014-12-13
4
109 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 35

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 39

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

773 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