Design question - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Design question

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-05-06 : 12:33:01

Hi, I'm writing an application where we collect information about a user in order to provide an auto insurance quote. Once we recieve the info we supply the quotes for a number of different companies. Depending on the rates, the user will choose a company that he/she wants. At this point the user must "complete" the application by providing additional information. Depending on the company this additional information varies. Let's say in retrieving the initial info we get info "A","B" and "C". Company 1 wants "A","B","C","D" and "E" while Company 2 wants "A","B","C","D" and "F".

I'm trying to figure out the best way to store the data. Our core tables store data all companies need ("A","B","C").
1) Should I create company specific tables that store the additional data the companies need? (to get company 1 data, join the core tables to the company1 tables)
Or
2) Create mini tables that only have the quote key and one additional item. Each question would represent a table. (To get company 1 data, join the core tables to all the sub tables that have company 1 data.)

Option one is nice since company 1 will have company 1 tables, so it would be fairly easy to see what info company 1 wants. Downfall is there would be some duplicate columns since company 1 and 2 might have some common questions. (There would be only one set of data though since the user can only choose one company at this point)

Option two is the most robust but it would be difficult to see that company 1 data comes from table54, table37, table88 (Obviously I would name the tables better but since there will be a number of tables you couldn't easily tell where all the data is stored. You would have to left outer join all the tables or something.

Either way there will be a lot of tables, I was curious if others had suggestions or another idea on how to store the tables.

I'm leaning towards option two since the sprocs would be the key to what data is returned/stored for each company. The sprocs would join the the appropriate tables.

Nic

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-06 : 13:27:14

Why not just have one table will all of the information, and have the columns nullable. If it isn't required for a company, then it wouldn't be supplied.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

nic
Posting Yak Master

209 Posts

Posted - 2003-05-06 : 14:09:07

Doesn't that kinda go against the rules of normilization? Having large tables with many nullable columns seems problematic. What happens if company "X" has one random question, then I need to add a column that is null in all other instances? Lots of null columns. It would work, but it doesn't seem very efficient.

It would be the simiplest solution though.

Nic

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-06 : 14:24:39

Well, I can't say that it does or doesn't because I don't know the data. I don't know how you would normalize A,B,C,D and F.

There certainly is nothing in the normalization rules against nullable columns. Having a table for each company with a bunch of redundant data isn't normalized either.

It doesn't have to be in a single table, normailize it as need be, but don't make a table for each company. What if the user goes back and selects a different company, they would then have to fill in some data that the already have (Unless you go through all of the table to see if that column is filled in for another company, not a very performant solution).

Also, normalization does not always equal efficiency. Sometimes we denormalize for the sake of performance.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-06 : 15:27:53

Create a table of all questions, 1 row per question, A-F.

Questions: (PK: QuestionID)
QuestionID
Question

If you would like for each question to have a list of drop-downs as answers, create a table of DropDowns:

DropDowns: (PK: QuestionID, PossibleAnswer)
QuestionID
PossibleAnswer

Now, you need to store for each company which questions they need answered:

CompanyQuestions: (PK: CompanyID, QuestionID)
CompanyID
QuestionID

Fill up that table as you have mentioned -- company1 gets A,b,c,d and e, company 2 gets a,b,c,d and f.

Now, for each user, when you collect answers to this questions, you can store them in a table. you could call it answers:

Answers: (PK: UserID, QuestionID)
userID
QuestionID
Answer

For the main form where this is filled in, you have drop down values available in your drop-downs table for each questionID. you could have more fields in your questions table, like datatype, or can you override the drop-down, etc.

Then, when a user picks a company, you can see which questions they still need to answer by doing a left outer join from Answers to Company Questions. just add missing rows and in your app present the questions that still need answers.

This way you can add companies and questions and everything else you need without ever changing your tables or data structure.

just an idea.

- Jeff

Edited by - jsmith8858 on 05/06/2003 15:28:31

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-07 : 00:00:09