Help on a Tables Design 3

[NervousRex]

Which design would I be better to go with?

Table A
ID ID2 Col1 ... Col50
Col1 thru Col50, not every column will have data, and there will be at least 20k rows to start, growing by about 40 a day. The most used query would return 1 row which would be found by ID = ID

or

Table B
ID ID2 ID3 Col1
In this idea, a row only exists if Col1 has data, but now the table size would be estimated at least 10x larger (200k). Estimating that half of Col1 thru Col50 in the above (Table A) have data. And would grow at a rate of 400 a day based on the estimate. And now each query would return multiple rows 1 to 50.




To me Table A sounds much better, but I want to be sure to use the right one. Basically 1 - 50 are questions on a survey, they are not required to answer and are open text, so we actually need to see what they say, not just a count of who choose choice A.

 

[ESquared]

I would say go with table B.

The number of rows returned in a single query is not the point.

What if you want to add new questions? You have to change all your queries.

What if you need to delete questions? You'll have to drop columns.

In terms of storage space, table B will use less! More actual data will fit in each data page.

With B you can create indexes that will speed up returning specific answers. With A you'd have to create an index for each answer (A REALLY BAD IDEA).

If you absolutely must have the data in rows you can use some of the CASE WHEN Aggregate techniques for pivoting tables in SQL Server 2000. Make it into a view. Updating the view to handle more columns would be much easier than changing all your code.

Trust me that A is NOT the way to go.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[Lamprey13]

You need to try and define your Entities first. The three you mentioned are Survey, Question and Answer. What the other entities you have I do not know. I would venture to guess that neither one of the options listed is correct, but TableB looks closer to the answer.

-Ryan

 

[ptheriault]

I second ESquared. You never want to have to alter a table because of a change to meta data. Also you'll get much better performance from table B as table A is way to wide!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NervousRex]

Thanks for the fast responses...

Well currently method A is what we are using, the only limitation is of course adding more questions, which can be done, just not as easily as method B would use. The others you mention are not a problem.

Here is what we currently do

Table 1
ID ID2 Col1 ..ANSWERS.. Col50

Table 2 Which relates the Answer to the Question
ID3 Col1
ID3 Col2

Table 3
ID4 ID5 ID3 Question

Table 4
ID5 Survey



So it works, but like you guys have said, it has wasted space when questions aren't answered.

 

[ptheriault]

I would have created it something like this.

Table A
Survey
----------
SurveyID INT PK
SurveyDesc VARCHAR(MAX)

Table B
Questions
----------
QuestionID INT PK
SurveyID INT
Question VARCHAR(MAX)

Table C
Answers
-----------
AnswerID INT
SurveryID INT
QuestionID INT
Answer VARCHAR(MAX)


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I hit submit to fast. If you wanted to assign the same question to multiple surveys just remove the survey id from the questions table. But using that schema do you see how you can add as many questions as you want without having to alter any tables.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

Perhaps you think the other points brought up are not problems now, but as your data grows they will become problems. At the point where they become terrible performance problems you have an existing system that needs fixing now and which will require a complete restructuring - always a bad thing. I vote that you fix it now before it becomes a problem and I think you really need to read up on normalization.


Questions about posting. See faq183-874

 

[NervousRex]

you have an existing system that needs fixing now and which will require a complete restructuring"

Exactly what we are doing, and not just to these tables, but the whole database. So I'll take the critizism as a complement actually, because of the fact that I did not just duplicate it a 3rd time. The fact that it was originally done this way is not solely my fault, as a previous developer did the design and I was only an intern, so what did I know? I'm still no DBA though, so thats why I ask. This will not be the last you see of me in the next few months.


So method B it is, thanks everyone!