SQL Server Database Design – Best Way to Store Products of Multiple Fields

You should create a table that holds the form and it has an form ID and the form name. Then another table that holds the questions for that particular form. The fields would be the formid from your first table and then the quesiton ID and the question. The question id would be continuous unique range of numbers (so if you have 3 forms and each has 10 questions, questionid needs to be 1 through 30). And finally a third table that has the questionid and the answer. You can also have other fields on these tables such as who took it, who created it, when it was created, when it was taken, etc.

I do not know exactly what you mean by 'updated many times' but I presume that you mean that the same ContactInfo can be present in multiple MyTable entries. In this case I would split it but put the ContactId in the MyTable entity. This way you are sure that all ContactInfo is the same everywhere it is used in MyTable.

The rule is that you put all fields that are identified by a single primary key in a separate entity. This way you minimize the number of updates when one of the fields that 'belong' to this primary key changes.

If 1 ContactInfo entry has only 1 MyTable entry and 1 MyTable entry has only 1 ContactInfo entry then the separation is not necessary. The only advantages are:

• If the length of an entry is big there is less chance that an entry does not fit in the physical block any more and needs to be 'moved' to an other physical block by the RDBMS.
• When you rarely need to select the ContactInfo and MyTable at the same time that (with a lot of rows) the fetch in of the rows goes faster since more entries will fit in a physical block.