Database Design for Dating Site

[mjuni]

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.

[timothybard]

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!

[mjuni]

Thanks for the input. I really appreciate it.

[Addy Watson]

It helped me a lot i am got a <website snipped> dating site from friend and i want to make similar to that site by this post i got so many things to learn that how to make a database to make you dating site.

I was little bit confused about the database but after getting your idea i am confident that i can do this.

thank you for help me in this area.

I would like to make some more idea clear from you would you please help me more ?

Thank you in advance.

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.

[arunjainmca]

its a very nice solution.

[tesuji]

Hi

like the beautiful ladies you are planning to manage there could be found so beautiful solutions on that web site.

-- tesu

[enlightenme]

Is there be a way to incorporate this to send each user that has a match a notification via email? Ex://

User A:

-Likes Hiking
-Enjoys Bowling
-Loves Skiing
-Drinks Mountain Dew

User B:

-Likes Rowing
-Enjoys Bowling
-Loves Skiing
-Drinks Mountain Dew

User C:

-Likes Rowing
-Enjoys Basketball
-Loves Skiing
-Drinks Orange Soda

If a user is found to have 2 or more things in common they are alerted via email saying you have similarities with user A, B, C

In this case user A would be messaged: User B has matching similarities to you
User B would be messaged "User A & C has matching similarities to you

[atifcomputerz]

good