June 17, 2009 at 5:49 pm
i have a excel worksheet that i need to create a database for. The design has me stuck.
All the information on the worksheet relates to a particular policy.
The information could then be categorized under two more headings; sections and questions.
policy_number
start_date
end_date
total
policy_number
Qtext
Qanswer
policy_number
Section_text
Sect_Ans
Damage
Lib
credit
subtotal
My problem is that I can't figure out how to relate the information in the
section and questions tables with a particular policy without adding policy_number to each table.
Thereby reaching a higher normal form of the database.
I want to figure out a way to remove the policy_number feild from the section and questions tables, but still relate each text and answer back to a particular policy.
June 17, 2009 at 9:19 pm
Why would you want to eliminate policy_number? That is what relates the data together? It looks like if forms the Foreign Key relationships you need between the tables.
June 17, 2009 at 9:27 pm
There is no need to remove the policy #
It
June 18, 2009 at 7:40 am
i'm trying to reach the first normal form as described http://www.informit.com/articles/article.aspx?p=30646
i'm trying to figure out how to remove policy number becasue there are multiple questions and answers for each policy. so the table would look like
policy_number, qtext, qans
11111, question1, answer1
11111, question2, answer2
11111, question3, answer3
22222, question1, answer4
22222, question2, answer5
22222, question3, answer6
in this db, there's the same questions asked for each policy. This causes the database to store duplicate information in the table.
anyone have a table stucture i can study that solves a similar problem?
June 18, 2009 at 11:55 am
The basics are Entities, Attributes and Relationships. Can you define all the Entities? Without knowing all the details I can see Policy, Question and Answer. Are there other entities? What attributes do those entities have and how do they relate? If you can define those, then you'll be well on your way to normalizing the schema, but that might take you beyond 1NF.
Also, it seems that you might be getting hung up a removing duplicate data. Yes, the policy number is duplicated, but if it is the way to uniquely identify a record, then it is not redundant data.
June 18, 2009 at 12:06 pm
foscsamuels (6/18/2009)
i'm trying to reach the first normal form as described http://www.informit.com/articles/article.aspx?p=30646i'm trying to figure out how to remove policy number becasue there are multiple questions and answers for each policy. so the table would look like
policy_number, qtext, qans
11111, question1, answer1
11111, question2, answer2
11111, question3, answer3
22222, question1, answer4
22222, question2, answer5
22222, question3, answer6
in this db, there's the same questions asked for each policy. This causes the database to store duplicate information in the table.
anyone have a table stucture i can study that solves a similar problem?
Okay, if you pulled policy_number from the table, how would you relate each question/answer back to a policy?
June 19, 2009 at 2:33 pm
How about a questions table (probably with a surrogate primary key
Then a policyQuestionDetails table with foreign keys to policy and questions -- probably best to store the answers here as well. If the answers are actually a choice between categories then you can link to a answers table but if they are free text then I think this is the best place to store them.
I've put a surrogate key in policyQuestionDetails but a combined key of questionId + policyId probably uniquely identifies a row.
Example
Tab (Policy)
===========================
[policyId] (PK)
[start_date]
[endDate]
[total]
Tab (Questions)
===========================
[questionId] (PK) -- Surrogate Probably
[questionText]
Tab (PolicyQuestionDetails)
===========================
[ID] (Pk) -- Surrogate
[policyID] (FK to policy.[policyId])
[questionID] (FK to questions.[quetionID])
[questionAnswer] ?????
[timestamp]
Charlie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply