paco
08-02-2011, 01:42 AM
Hi,
I'm sorry to ask about something which has been asked so many times before but I've read a lot of the threads and I'm still not clear about the best way of going about this.
I have a questionnaire with fields such as:
client id (number)
client name (text)
date of appointment (date)
location (text - select from list)
fee (number)
additional requests (free form text box)
satisfaction (1,2,3,4,5)
would recommend (yes/no)
My understanding is that there are two possible approaches here:
Flatfile
Make each question a field name and have 1 table with 1 record per client
Relational
Several tables along the lines of
tbl_clients: client_id, client_name
tbl_questions: q_id, q_datatype, q_content
tbl_answers: q_id, client_id, answer
Everyone seems to say the relational approach is better because it is more normalised so the data can be analysed more easily. However there are several aspects of implementation I can't see how to get working.
With a flatfile database I can design a form which is laid out prettily and has appropriately shaped boxes for each question. In the relational approach I would presumably need to have some kind of continuous sub form that reads out all the questions from the questions table. Do I lose all control over pretty layout?
What if I wanted a tabbed form with related questions on each tab? I suppose I could add a field to the questions table which tells it which tab it belongs to but that sounds like mixing up presentation and data which I've also heard is bad.
And there's still the problem of data types. Since the questions have different types of answers they need different kinds of boxes in the form. Do I have to use scripts in the form to look at the data type field and change the input box appropriately?
I'd be grateful for any advice you can give me. I still feel like I might have got the wrong end of the stick. If there is already a post with answers to these questions I'd be grateful if you could paste a link to it.
Thanks
Paco
I'm sorry to ask about something which has been asked so many times before but I've read a lot of the threads and I'm still not clear about the best way of going about this.
I have a questionnaire with fields such as:
client id (number)
client name (text)
date of appointment (date)
location (text - select from list)
fee (number)
additional requests (free form text box)
satisfaction (1,2,3,4,5)
would recommend (yes/no)
My understanding is that there are two possible approaches here:
Flatfile
Make each question a field name and have 1 table with 1 record per client
Relational
Several tables along the lines of
tbl_clients: client_id, client_name
tbl_questions: q_id, q_datatype, q_content
tbl_answers: q_id, client_id, answer
Everyone seems to say the relational approach is better because it is more normalised so the data can be analysed more easily. However there are several aspects of implementation I can't see how to get working.
With a flatfile database I can design a form which is laid out prettily and has appropriately shaped boxes for each question. In the relational approach I would presumably need to have some kind of continuous sub form that reads out all the questions from the questions table. Do I lose all control over pretty layout?
What if I wanted a tabbed form with related questions on each tab? I suppose I could add a field to the questions table which tells it which tab it belongs to but that sounds like mixing up presentation and data which I've also heard is bad.
And there's still the problem of data types. Since the questions have different types of answers they need different kinds of boxes in the form. Do I have to use scripts in the form to look at the data type field and change the input box appropriately?
I'd be grateful for any advice you can give me. I still feel like I might have got the wrong end of the stick. If there is already a post with answers to these questions I'd be grateful if you could paste a link to it.
Thanks
Paco