Hi,

I am doing up a DB Design for a survey application. Each question in the survey has a couple of options. These are usually not the same across questions.

I have a lookup table for each set of survey options with a link to the main table. This is forcing me to create a lot of small tables.

Is this good design?

It will be a SQL Server 2005+ ASP.NET Web app

Thanks,
J

As long as you are Normalising correctly, and the many small tables are the a result of that, then it is correct. SQL Server is quite capable of handling many small tables, in fact since they are simple reference or lookup tables, they will be read frequently, and they will remain in cache. The result is they are very fast.

The correct Normalisation will allow ordinary Declarative Referential Integrity (which is otherwise difficult). This will ensure that the values in the child tables are correct. Additionally, when the choices for each survey question change, the changes are easy to implement (without recoding).

On a different level, ensure that the answers to questions are not separated simply because they are separate questions. Eg. you will have one table for Gender, that will service the needs of any survey question related to Gender.

Why reinvent the wheel? why not use a free web based questionnaire tool? The advantages of doing this is that they'll have done all the work already, it will probably look better in the end and you can immediately start getting results. You also won't have to keep altering a database every time you want to add a new type of question.

Having lots of lookup tables is definitely technically correct and allows you to use FK however setting up each new question means : inserting the question, creating a new lookup table for the responses, inserting the valid responses, setting up a FK from the question to the response table. This will quickly become painful.

Alternatively you could just have a single question table and a single response table holding the valid responses for each question. This means you just insert the question and then insert the valid responses. The advantage is that the database structure is now static and only the data changes when you add new questions. This also makes it easier to write the screens to ask the questions, enter the questions and enter the responses. The downside is you can't use a FK to automatically check that responses are valid so you need to check responses are valid before inserting them (I'd just write a small function).

Which strategy do you think these free tools use?

No, in this case creating separate tables is not a good idea. It means that you will need to alter your schema (create additional tables) with every new survey you add. That is definitely against best-practice database design.
Your data is NOT normalized the way it is. You should examine it, and come up with a way to store it in a defined set of static tables.

I believe he's currently creating a new table every time he adds a new question (with new options). If you still want to go ahead and roll your own then it might be worthwhile showing us the relevant DDL and we can make suggestions.

If he is creating a new table with every question, that is even worse.

That's not correct.
1 Setting up a new question which is similar to the questions that already exist, with answers that already exist, means inserting one row in the Question table, only.
2 Setting up a new question which is NOT similar to the questions that already exist, but with answers that already exist, means inserting one row in the Question table, with appropriate (existing) FKs, only.
3 Setting up a new question which is NOT similar to the questions that already exist, with answers that do NOT exist, means adding a new answer lookup table; inserting one row in the Question table, with appropriate (new) FKs, only.

Databases are not cast in stone or concrete. Given that the database exists, that would take me a few minutes. i do not understand the "pain".

That is exactly why they are free. They do not represent a valid model for comparison with a corporate database (unless you want a free, throw-away "database").

If data integrity and referential integrity are not important, then fair enough, go ahead with a single question and single response design, but don't call that a "database", and do not expect "database" capability or power out of it.

Most questionnaires I’ve completed involve different questions with different answers (3) and this is where the problem lies.

Sure altering the structure takes minutes - the pain comes from the results of altering the structure:

• Would you continually sit next to the user ready to alter the DDL each time the user enters a question or would the program automatically alter the structure of the database automatically?
• Would you backup your database after each structural change?
• How will you link your answer table to these various lookup tables. Using an FK implies that the response field will link to a single lookup table. How are you going to link the response field to multiple lookup tables without having multiple response tables (ie yet more structural change)?
• How would your front end pull the valid responses for a given question if there are 100’s of lookup tables? and how would it work if multiple questions share same the same lookup table?
• How would you ensure that lookup tables are still being used after questions get deleted?
• At what point would you decide your design is untenable – after you get to 100 lookup tables, a 1000?

I'm sure we'd all like to say we could produce something fantastic but in reality what features would you or the OP be adding to your questionnaire that aren’t already in these free systems (example)? Will your system look any better? And how long will it take to implement all this? How much will it cost? Most of the questionnaires I’ve seen put out by large companies are actually rather sad in comparison to these free offerings.

The questions and the valid responses are pulled from just two tables by the front end. The responses get checked against the response table before being inserted. Where do you figure the illegal responses are going to come from? Will this system produce more bad data than the organised chaos of your system? It’s far easier to build a powerful system on top of a solid unchanging structure where different responses are modelled in data rather than by changing your structure each time.

Mike

Certainly looks like you just like to argue with me. Most of your issues are too peripheral to respond to, and if I did, we would go off on another tangential discussion.Why don't you put up a real example (2 "tables" or 10 tables) and we can discuss Data and Referential Integrity, the vulnerabilities, in specific terms.
Dramatic descriptions, opinion without fact. In any case, for a 2 "table" proponent, any Normalised structure would have a very high EekFactor, too many boxes, "chaos" indeed. In order to resolve or close the issue, instead of mere argument re personal opinion, it would be good to stay away from opinions and colourful imaginings, and stick to the technical facts. Again, an example that can be worked through for both options, is necessary. Maybe you provide the 2 "table" structure, and I will provide the Normalised structure, what do you say ?
Well, if you Normalise it, you will get a solid structure that does not change (extensible, but no structural change); if you have a 2 table or unnormalised or denormalised structure, it will keep changing. So you are fighting against yourself on that point.

Er, I never said you had to change structure all the time.

• just because others say that that is the result of a Normalised structure doesn't mean it is true
• from this and other threads we already know that you have perceptions and interpretations which do not match the facts, and colourful opinions, so just because you say so doesn't mean it is true either
• there is a lot of misiniformation on this site
• some people have their own private definitions of standard technical terms (such as Normalisation)
• it is therefore not possible to have a reasonable debate
• therefore I will stick to items I have posted, technical facts, terms from textbooks written by authorities
• and stay away from unfounded opinions, colourful as they may be
• particularly when it has nothing to do with what I have posted

When new questions are added, exactly what needs to be inserted; and when the structure needs expansion (not re-structure!); has already been posted by me; read it again, repeating it will not make it any more or less real.

Yes, in a Normalised structure, different questions and responses are implemented in data, not structure. It is not a 100% black-or-white issue as you apparently think it is.

I suspect if you spent the time to try and answer my questions then you'd see why your design won't work. Simply labelling questions you don't like as peripheral or impertinent doesn't fool anyone.

This is just a quick guess at the "2 table" design. I've added a couple of tables to give it some context. It's a bit simplistic but it should be enough to go on:

Code:

Tables:
   Headers:        h_id, header_txt, seq_no
   Questions:      q_id, h_id, question_txt, seq_no, mandatoryYN, pattern_str, 
                   dependant_q_id, dependant_ans 
   ValidResponses: q_id, ans, defaultYN
   UserResponses:  u_id, q_id, ans

Proc:
   add_user_response( u_id, q_id, ans )
        if mandatory and ans = “” -> error
        if exists( valid responses for q_id )
             If ans not in ( valid responses for q_id ) -> error
        else
	     If pattern and ans not like pattern -> error
        add ans to UserResponses table

Some example SQL:
   select ans
   from   ValidResponses
   where  q_id = 123

I added a Headers table just in case the user wants to segregate the questions under different headers but it's not necessary. The italic fields in the Questions table are optional depending on what features you want to add - I'm sure I could come up with more. Answers would be added using a procedure to check the validity against the ValidResponses table. If the answer is free text then there'd be no entries in this table. If the free text needs to match a pattern then I added an optional pattern to Questions (ie for zip codes, numbers etc). If we need the same responses for a number of questions then we could add an optional standard_q_id into the Questions table that would point to these responses. I also added the dependant fields into the Questions table just in case a question should only be asked if a user has responded in a certain way to a previous question.

The advantage of this design is that it's easy to expand, it doesn't require any new tables to be created each time a question (with new responses) is added and it's easy to build a front end around the system. On the downside the data is all stored as text (I don't see this as an issue in a questionnaire system) and the referential integrity is manual. On the plus my design will work in the real world and will be easy to expand to 100 questions or 1000 questions while using separate lookup tables will not.

EDIT : added pseudo code for proc to hopefully make logic easier to understand

Allow me to go first. Here's a good example of:

• the application area relating to the question
• Normalisation
• cut down to the issues discussed in the thread (there are more columns and tables in the real database)
• from a real world implementation, now in production at a multi-national market survey company (like A C Nielsen)
• this was one of those instances where OLTP and DSS could be served, from the same database, without duplication (but let's not get side-tracked)
• the OLTP uses a faithful Relational design; the DSS uses Dimension/Fact (or Star Schema) laid over top of the OLTP.

Blue = Reference/Lookup (Dimensions for the Dimensionally inclined)
Yellow = TransactionHeader/Identifier
Pink = TransactionDetail/History (the Fact tables for the Dimensions); these are usually aggregated, so that element is designed for

Re Normalisation, it is not perfect or extreme, it has many design decisions re practicability (compromises, ease of use: for the DBA; the app team; and the users, who use a few different 3rd party report tools).

Some people in this forum think users cannot or should not use the database directly. In my universe (banks and financial institutions, thousands of users), that would unacceptable and justification to cancel the project and the payment. No, we have thousands of users, using a variety of report tools (Excel to Crystal to BusinessObjects), accessing the live database (not a "copy for reporting"). That's because it is Open Architecture, standard-compliant, secure and stable.

Being a big multi-national market survey company, and making lots of money with their data, it is Precious to them. They are not about to change the structure (extend, yes, that is expected; but not replace or change the structure). No "refactoring". No developers telling the users what they can or cannot do with their corporate data. The business tells the developers what they can and cannot do.

It should be noted that I spent twice as much time on Extract-Transform-Loading the old files into the new database, as I did implementing the new database and universe. Gratefully, that never has to be done again; the data is available in straight Relational form.

Re Performance, depending on the query, it runs a minimum of 10 times faster than the db it replaced, and a maximum of 120 times faster. (Some reports ran thousands of times faster, but that was due to indexing errors, as opposed to lack of Normalisaion, so I excluded those from the previously mentioned figures.) And yes, the "database" this replaced was one of those classic "denormalised for performance, designed by a developer" jobbies.

Re Data and Referential Integrity, the lack of that was the single factor driving the business to re-implement; poor performance was second. They could not even get the same results in reports from day to day. We switched from "data and RI are fully implemented in the app code" (code for "there is no data or RI"), to "Data and RI are implemented declaratively in the database, and enforced by the server".

In the cut down version, I have removed columns that are not relevant to the thread.

jww_van: yes, thankyou, all the TransactionHeader tables have modifiedby, modifiedon and moodifiedusing; but I have naming conventions, so I call them UpdatedTimeStamp, Created|UpdatedUserStamp, etc. Versioning and keeping several UAT copies of the database current is a no-brainer, and has zero performance impact. Anyway, all that is not relevant to the thread

However, in order to provide fertile soil for discussion, keeping in mind the original question regardss (is pertinent to) "Loads of Lookup Tables", I have retained:

• Lookup/Reference tables that are used for non-questions only
• Lookup/Reference tables that are used for both questions and non-questions (note the re-use)
• Lookup/Reference tables that are used for questions only
• one subject area (firearm use) to show reasonable Question/Answer complexity
• Questions that are simple (no Lookup/Reference tables); referenced; and complex (as per subject area)
• columns touched on in the thread, but not yet resolved, which will hopefully provoke thought and discussion

• The Null Problem hasn't been dealt with in this thread, however it is dealt with in the database (in order to avoid getting side-tracked, let's stay away from that little nugget until we cannot any longer)

Please, as per the issues raised in the thread thus far, let's contain the discussion to:

a. Normalisation (as per textbook, not foreign definition)
b. evaluate if the goals of Normalisation were realised
c. Data and Referential Integrity
d. ease of adding questions (of various types)
e. ease of extension of the database structure (minimised effect on existing app code and report definitions)
f. ease of writing app code (watch the EekFactor)

(I also wrote the BusinessObjects Universe, which the "power" users use; that was considered to be within the Data Architects domain, not the developers. Although I wrote a few complex reports to prove the Universe, in the main, the users wrote their own reports using the Universe or the database directly.)

Mike

Your turn. Give us your 2 file design, and let the discussion begin.

Simply labelling designs you do not understand using dramatic language and presenting opinions not based on facts doesn't fool anyone either.
Evidently (by virtue of the evidence contained in your posts; and your submission) you are speculating (have no actual experience) about the "real world" relating to the application area. The real world I work in has 1,000s of surveys; 100,000s of Questions; close to 6 billion Answers; 1,000s of users (it is web-enabled so that survey customer can query the database themselves). At any given time, they have 200 backpackers (cheap casual labour for a thankless job, no offence to backpackers) on the phone, calling people, entering surveys.

Now I realise it is not obvious to those who have not worked in the application area, or supplied aggregation and DSS type functions on large commercial databases. Maybe I should have mentioned (in my post #11): One key goal/requirement of the database (actually an ordinary capability of any real database) is:

• we do not want to have questions (in different surveys) repeated
• we want the same questions (in different surveys) identified as such
• so that we can then query the database re:

• "how many people {currently|previously|ever} own firearms [of various kinds]" across all the surveys that captured that information.
• "how many people have had more than q partners" ditto
• "how many people desire an income level {higher|lower} than present" ditto

• it is a second generation database, not a data heap. They were already used to basic aggregation in the old "database"; they wanted DRI and performance; not less or no aggregation
• importantly, that was supplied via ordinary Normalisation, no additional design

There you go speculating about conclusions about what is and is not easy. Let's leave that until after we tackle all the issues that result in the conclusions, "easy" or not. Right now it is premature.

Now that you have supplied a 2 file design, thankyou, I could launch into a comparison, but it may not be fair. Why don't you take another stab at the 2 file design, you know, something that can be reasonably compared with my submission; otherwise we do not have enough specifics on your side to compare against. Let's deal with the technical facts (as per post #11) on specific items, first; let's leave telling us the conclusions until the end. No use repeatedly trumpeting the conclusions without facts to base them upon. Notice, I have refrained from telling you how superior or mature or whatever my submission is.

By the way, since you were presenting your opinions about the technical subject, and the application area, with such certainty and colour, I expected that you have actual experience in those areas. I am shocked to find out you do not.

Your design is exactly as I expected so my comments haven't really changed much - but I'll repeat them for you anyway:

1. To add a new question you need to add a new answer table and a new lookup table for the answers and a new field in the main answer table to say what type of answer it is. In the firearm question you actually added 2 new lookup tables. This works fine on a 4 question questionnaire but what happens when we have a 100 questions requiring 200 new tables. Remember it's not the number of tables themselves that's the issue but the code required to support all this.
   
2. Who adds these new tables? do you sit beside the user and enter the DDL every time the user thinks up a new question or is the user content with a static questionnaire.
   
3. How are the valid responses added into the lookup tables - do you have a screen for each new table?
   
4. How do you actually get data from this design ie select all the responses that a user gave to a questionnaire.
   
5. How would the entry form get the valid responses to any question on the questionnaire?
   
6. Does the developer of the front end have to redo his code every time a new question (with new responses) gets added?
   
7. I assume that if you want to use this design for a different questionnaire then you'd just have to scrap all the tables and build a completely new set? Would the code for the front end also get scrapped as well? And any reporting code?
   
8. At what point would you decide your design is untenable – after you get to 100 lookup tables, a 1000?

I'm sure you have loads of experience in this area but I'm just trying to see if your design is correct for the OP. Using your design then your 100k questions would turn into 200k tables (AnswerEmployment, EmploymentType ...) and the main answer table would have more than 100k fields (IsEmploymentType, IsGender ... ). 6 billion answers entered by 200 backpackers implies they type very (extraordinarily!) quickly.

I'm a database designer. I tend to produce financial databases rather than questionnaires but, even so, I believe I can see many issues with your design as it relates to the original question. Issues you still refuse to address!

Questions (q_id, h_id, question_txt, seq_no ) :
1, Salutation, 1
2, Name, 2
3, Employment, 3
4, Gender, 4
5, What firearm do you carry, 5

ValidResponses (q_id, ans ):
1, Mr
1, Mrs
1, Ms
1, Dr
3, Employed
3, Self Employed
3, Unemployed
3, Retired
4, Male
4, Female
4, Unknown
4, Hermaphrodite
4, Trans Gender M-F
4, Trans Gender F-M
4, Will Not Declare
5, Rifle Single Shot
5, Rifle Bolt Action
5, Rifle Automatic
5, Shotgun Single Shot
5, Shotgun Magazine
5, Pistol Revolver
5, Pistol Magazine

PS just curious - what's the difference in the Gender table between Unknown and "will not declare"?

Seriously? You do not understand the "pain" of pushing a schematic database change through dev, qa, uat, and production environments? You are now aware of the hassle of regression testing, smoke-testing, sign-offs, etc?
Have you not ever worked in an enterprise software environment before?
Schema changes are to be avoided at all costs.

agree with blindman, you have to do good design initially.Don't expect that you have chance to update schemas frequently or largely.

Use UML or ERWin to do so. Make sure your system design follow some patterns, such as composition / generalization stuff.

Also when you do design, try to split data and application - I mean you should use SOA thought in your db design, keep loosing coupling with your application so your db schema will be more portable, scalable later on.

Wayne
Pangosoft.ca - Expert on web site migration, url redirection, database migration, database architecture design and management consulting