help normalize; remove a column that relates info

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

help normalize; remove a column that relates info

Author

Topic

techstress
Starting Member

3 Posts

Posted - 2009-06-17 : 20:03:01

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.

techstress
Starting Member

3 Posts

Posted - 2009-06-18 : 09:45:31

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?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 11:10:14

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
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

drkusnadi
Starting Member

2 Posts

Posted - 2009-06-18 : 18:34:48

what's the relationship between the tables? can a policy have multiple question or a question has multiple of policy? you just need to add a primary key for each table. i.e. policy_number, Q_id, Section_Id.

so if the relationship is one to many, e.g. a policy can have multiple questions and a question can have only one policy, then you add the policy key to the question table. visa versa.

if the relationship is one to one, you might as well have all in one table. else, if the relationship is many to many, you can have an additional table e.g. policy_question, to hold the ID from both policy and question table.

hope that answer the question.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-19 : 04:13:27

quote:

if the relationship is one to one, you might as well have all in one table. else, if the relationship is many to many, you can have an additional table e.g. policy_question, to hold the ID from both policy and question table.

Even if it was one to one I'd still go with a seperate questions table. Otherwise you end up with data like this:


Policy No | Question                               | Answer
--------------------------------------------------------------------
1         | "Any convictions in the last 2 years?" | "No"
2         | "Any convictions in the last 2 years?" | "No"
3         | "Any convictions in the last 2 years?" | "Yes"
..
...

So you are:

a) Storing a *lot* more information than required if the Question was replaced with a [quesion_Id] Foreign Key.

b) Having to update a *lot* more information if you need to change the question text

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

techstress
Starting Member

3 Posts

Posted - 2009-06-19 : 13:28:51

quote:
Originally posted by Transact Charlie

How about a questions table (probably with a surrogate primary key

...

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, that's pure genius. I think this is what i'll go with and model a second set of tables for sections data.

I'm not sure what a surrogate is though.

trondaron
Starting Member

13 Posts

Posted - 2009-06-21 : 18:18:03

I believe he is referring to this type of surrogate: [url]http://en.wikipedia.org/wiki/Surrogate_key[/url]

In short it means that the Key is not really based on the data, it's just an incremental ID used for table relationships.

In your case policy_number(policy_id) is a number actually used by the system's users so is a natural key [url]http://en.wikipedia.org/wiki/Natural_key[/url]. Whereas questionID is created by the database whenever a new row is added to the Questions table and has no relationship to the data (other than it has been assigned to represent a question) so it is a surrogate key.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 04:34:06

Yes -- exactly. Sorry if that wasn't clear.

Every table should have a primary key to uniquely identify a row. When no natural key exists then a surrogate should be used (most of the time this is an identity field or a GUID (globally unique identifier). By default the primary key is implemented with a clustered index (which is the actual structure of the data).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources