How to store Profile Questions

In my User Profiles, I would like to have a series of questions that people can choose to answer. The goal is to help Members to get to know each other better, and to help me to build sub-communities and customize my content based on how people answer.

For example, maybe a lot of people have S-Corporations? Or maybe a lot of people have Family Businesses? Or maybe most of my Users provide “Services” versus selling “Products”?

Here are some sample questions…

1.) Why do you like working for yourself?
2.) What does your business do?
3.) If you could start over, what would you do the same?
4.) If you could start over, what would you do differently?
5.) As a Small-Business, how do you compete with Corporate America?

Questions:

1.) How should I store the answers to these questions?

In my “member” table?

In a separate table?

2.) What do I call the fields?

“q01”, “q02”,… ??

or do I try and give them some longer, self-identifying name like “like_working_for_self”, “what_does_bus_do”?

3.) What would be a reasonable field size?

I am thinking varchar(1024).

Or should I use “Text” and let them type a book?! :-/

Debbie

  1. separate table
  2. qno, answer
  3. text is fine

It seems to me that I have a many-to-many relationship…

  • One Member has many Profile Questions.

  • One Profile Question has many Members.

  • The intermediary table would be the “Profile Answers” table.

For example…

Members:
ID Name
01 Bob
02 Kim
03 Larry

Profile_Answers:
Member_ID Question_ID Answer
01 01 Because I like being my own boss and doing things my way!
01 02 Get a good attorney and a good accountant!!
01 03 I beat out the big box stores with superior customer service and by knowing my customers personally

Profile_Questions:
ID Question
01 Why do you like running your own small-business?
02 What advice can you share with someone wanting to start their own business?
03 How do you compete with mega corporations?

How does that look?

In addition, can I use this same logic for my base Member Attributes like…

  • Location
  • Interests
  • About Me

Debbie

fine