Hi all,
I could use some help is re-designing my database.
At present I have created a database and this has been successfully working so far. An update in due and now I am stumped on how to include this in my database.
Outline:
The database structure has been attached.
The area of question is related to TblVIQ and TblObs.
TblVIQ - This table lists out a series of questions of a questionnaire, each with a Question No (VIQNo). The questionnaire at present has 3 versions (2009, 2011, 2012). 2012 edition is the one in use. The questions are indicated by a Yes/No under the column of 2009, 2011 or 2012 to indicate which version. Sometimes the VIQNo changes even though the question remains the same in different versions. Hence the question is recorded as a separate record and indicated with the correct marking in Yes/No. (Attached sample of TblVIQ)
There is a column VIQ2009ID which is basically referencing to within the same table - 2012 questions to the 2009 questions. For e.g. on page 1 of attached sample TblVIQ, VIQID 827 (row 3), VIQ2009ID shows 2 (VIQID 2) which is the question but in different versions.
The reason that this was set up this way is that
1. TblObs - Each Observation is referenced to a VIQNo depending on the version used. At present only the latest edition is in use (2012).
2. The reason an internal cross reference was used within the same table is to list observations for e.g. when VIQNo 1.2 is selected (2012 by default), this should list all observations of VIQID 827 and also VIQID 2
Question:
A new 2013 is expected. Is the above structure ok, or is there a simpler way to do this.
Thanks a ton for the help in advance
I could use some help is re-designing my database.
At present I have created a database and this has been successfully working so far. An update in due and now I am stumped on how to include this in my database.
Outline:
The database structure has been attached.
The area of question is related to TblVIQ and TblObs.
TblVIQ - This table lists out a series of questions of a questionnaire, each with a Question No (VIQNo). The questionnaire at present has 3 versions (2009, 2011, 2012). 2012 edition is the one in use. The questions are indicated by a Yes/No under the column of 2009, 2011 or 2012 to indicate which version. Sometimes the VIQNo changes even though the question remains the same in different versions. Hence the question is recorded as a separate record and indicated with the correct marking in Yes/No. (Attached sample of TblVIQ)
There is a column VIQ2009ID which is basically referencing to within the same table - 2012 questions to the 2009 questions. For e.g. on page 1 of attached sample TblVIQ, VIQID 827 (row 3), VIQ2009ID shows 2 (VIQID 2) which is the question but in different versions.
The reason that this was set up this way is that
1. TblObs - Each Observation is referenced to a VIQNo depending on the version used. At present only the latest edition is in use (2012).
2. The reason an internal cross reference was used within the same table is to list observations for e.g. when VIQNo 1.2 is selected (2012 by default), this should list all observations of VIQID 827 and also VIQID 2
Question:
A new 2013 is expected. Is the above structure ok, or is there a simpler way to do this.
Thanks a ton for the help in advance
