I am tasked with designing a dating site in asp.net with VB using sql server 2005 as the database platform. I have not had a lot of advanced database design experience so I am hoping someone can help me out with this one....

For each user there is a profile (some profile questions might be: nationality, education, age, eye color, etc, etc..). Each of these profile questions could either contain a single value from a radio button (example: are you male or female), multiple values from a list of checkboxes (example: check your hobbies and interests) or text (ex: tell in a short paragraph what you are looking for).

Does anyone have any advice as to the best way to design this. Here are some of the options I am considering:

1) Create a different table for each profile question:
example:

EyeColor
--------
UserId (int or guid)
EyeColor (varchar)

Interests
---------
UserId
InterestValue

I see this as being a problem because there could be upwards of 50 table joins for each search query and the database would need to be updated if a profile question is added or no longer used.


2) Put the entire profile in one table:
example:

Profile
-------
UserId (int or guid)
HairColor (varchar)
EyeColor (varchar)
LikesMovies (bit)
LikesHiking (bit)

I see this a being a problem as this table could contain up to 70 columns and I think serially searching through this one gigantic table could be very slow if the user base gets large. Also as with the above scenario, the DB would need to be changed to accomidate any additions/deletions to the profile questions.


3) Ideally I would like to design a flexible structure to allow questions of different types (radio, check box, text box) to be added/deleted from an admin interface without having to modify the db structure but I don't know how to design this so it would be efficient for searches.

Another dilema is that I would like to avoid using dynamic SQL, but I dont know how I could search accross a varied number of parameters with a stored procedure.


Could anyone give me some advice on any of the above.

Any advice would be appreciated. Thanks.

This is my recommendation:

Create one 'user' table that stores, at a minimum, UserId, but could also store name, DOB, etc.

Create one 'Attributes' table that stores all the different "questions" ("What is your hair color","What is your eye color","What are you favorite activities", etc.) This table needs an ID field and a text field to hold the question

Create an "Responses" table that stores the choices for each 'Attribute'. This table needs an int field to join to the ID of the Attributes table and a text field to hold the responses to the questions As you can see, the responses for all questions will be in this table.

Create a "user responses" table to store the answers for each of the users. This table will have an int field to join to the user ID and an int field to join to the responses table.

Once those are set up, to find all the responses a user entered for his or her profile, create a query to select the user, join it to the "user responses" table and join the "user responses" table to the "Responses table". The user table holds the user name, the user responses table holds the answers, and the responses table hold the text of the answers.

This method is very flexible because to add a question, you just need to add a record to the "Attributes" table and add the responses for the question to the the "Responses" table. Also, each question can be answered with multiple answers.

Additionally, this method is very flexible in the different queries you can create... for example, you can create a query listing all users who answered a question with a specific answer, you can see which answers are the most popular answer.

The only issue with this method is that you need to handle text answers separately; this method only works for multiple choice answers. However, using this method and mixing it with a text answer method should not be difficult.

Let me know if you have any questions!

Thanks for the input. I really appreciate it.