Hello everybody,
My organisation runs a paper-based survey. I'm currently designing the database to put the data in once we receive it from our data processing contractors (they give us a uncleaned version).
I've got a plan for this database, but I want to make sure I'm doing things right before proceeding (I'd hate bad database design to cause us problems down the line).
For the survey, there are a total of 9 different survey forms, and respondees get a different form depending on their characteristics.
The first half of each form is common between the 9 forms, and then the second half differs depending on which form you are given.
Because the first half of the form is common, we want to do some analysis on these common parts on the whole dataset (all 9 forms combined).
There are also some parts of the second half of the forms which are common to several forms (but not all forms) and so I'd like to be able to do analysis on, say, the dataset for form type 1 and 2 combined.
Each form has a unique ID, which is assigned by our data processing contractor.
My suggested approach: A main table containing the common data, linked using one-to-one relationships on the unqiue form ID to nine seperate form specific tables. These nine form-specific tables would contain the form specific fields (which differ between forms).
I'd be really grateful if anybody could offer their view on whether my suggested approach is the most sensible way forward. A second approach would be to have a single table with all possible fields in it, with many blank fields for each record - e.g. a record for form 9 would have blank entries for all of the form-specific fields for forms 1-8.
Thanks very much for any advice, I'm very grateful.
Kind regards,
Robin
My organisation runs a paper-based survey. I'm currently designing the database to put the data in once we receive it from our data processing contractors (they give us a uncleaned version).
I've got a plan for this database, but I want to make sure I'm doing things right before proceeding (I'd hate bad database design to cause us problems down the line).
For the survey, there are a total of 9 different survey forms, and respondees get a different form depending on their characteristics.
The first half of each form is common between the 9 forms, and then the second half differs depending on which form you are given.
Because the first half of the form is common, we want to do some analysis on these common parts on the whole dataset (all 9 forms combined).
There are also some parts of the second half of the forms which are common to several forms (but not all forms) and so I'd like to be able to do analysis on, say, the dataset for form type 1 and 2 combined.
Each form has a unique ID, which is assigned by our data processing contractor.
My suggested approach: A main table containing the common data, linked using one-to-one relationships on the unqiue form ID to nine seperate form specific tables. These nine form-specific tables would contain the form specific fields (which differ between forms).
I'd be really grateful if anybody could offer their view on whether my suggested approach is the most sensible way forward. A second approach would be to have a single table with all possible fields in it, with many blank fields for each record - e.g. a record for form 9 would have blank entries for all of the form-specific fields for forms 1-8.
Thanks very much for any advice, I'm very grateful.
Kind regards,
Robin
Last edited:
