Re: Too detailed DB? Need advice.

<jarlaxle00_at_hotmail.com> wrote
| I'm making a database for a polling system and was unsure how to setup
| the database. I want to set it up so that the questions and options
| can be reused without replicating the intormation in the database.
| For example, the question "What is your favorite new movie?" and/or
| the option "Yes," if re-entered, will simply use the question/option
| already in the db. This is the table structure I came up with:
|
| Questions:
| QID
| Question
|
| Options:
| OID
| Options
|
| Polls:
| PID
| QID
| Date
|
| Poll_Options:
| POID
| OID
|
| Results:
| POID
| Number

You're nearly there, but you lose some information.

Question: (Q_ID, question)
Option: (OPT_ID, opt_text )
Poll: ( POLL_ID, name, date, participant_nmb, ... )

The Poll table and question-table obviously have an n-n relationship, so we must insert a table between them to maintain that relationship.

Poll_Question: (POLL_ID, Q_ID )

Now, the results of a poll relates to a certain poll, a question asked in that poll, and an option. The number of people ticking that option is a natural attribute here.

Poll_Result: ( POLL_ID, Q_ID, OPT_ID, number )

You can see that the Poll_Question table is kind of superflous; the information this table provides is extractable from the Poll_Result table. But there is no error to keep such a table, it most certainly clearifies the model, and it may be a rule in the real world that a set of questions should be tied to a poll prior to the provision of the results.

--

Thomas