Database Design: User Profiles, e.g. at Meetup.com

Question

At Meetup.com, when you join a meeting group, you usually need to fill out a profile for that specific group. For example, if you join a group of videos, you may need to list the movie genres that you like, etc.

I am creating a similar application in which users can join different groups and fill out different profiles for each group. Assume 2 possibilities:

• Users can create their own groups and determine what details to ask users who join this group (so something is a bit dynamic - perhaps assuming at least an EAV design is required).
• The developer now decides which groups to create and indicate, which details to ask users who join this group (this means that the profile data will be predefined and "hardcoded" in the system).

What is the best way to model such data?

More detailed example:

The Videos group will ask its members to indicate the following:

• Name
• Date of birth (used to calculate the age of the participant)
• Gender (must be selected from "male" or "female").
• Favorite genres (you must select 1 or more from the list of specified genres)

Extreme Sports will ask its member to indicate the following:

• Name
• Description of activities (descriptive form)
• Postcode

The bottom line is that each group may require different details from members joining their group. Ideally, I would like someone to create a group (ala MeetUp.com). However, I also need the ability to request participants well enough (for example, to find all the audience for women aged 25 to 30 years).

Answer 1

For something like this .... you need maximum normalization, so you won’t have duplicate data anywhere. Since your user tables may contain the same record type, I think you might need 3NF for this.

My suggestion would be to blow up your tables so that you have something close to 6NF with EAV, so that each question that users must answer will have their own table. Your user-created tables will then link to one of your question tables. This avoids data duplication. (For example, you do not need an entry in the “MovieGoers” group with the name “John Brown” and one in the Extreme Sports group with the name “Johnny B.” for the same user, t want him to be “what is your favorite color "answered" Blue "in one group and" red "in another. Any data that might cover groups, such as general questions, would be normalized in this form.)

The main disadvantage of this is that you will have many tables, and you probably want to create views for your statistical queries. However, in terms of pure data integrity, this will work well.

Note that you could probably leave out with factoring the common fields if you want. Examples of common fields include name, location, gender, and others; You can also do the same for general questions, such as "your favorite color" or "do you have any pets" or something like that. Group questions that do not cover groups can be stored in a separate table for this group, not blown up. I would not recommend this because it would not be as flexible as the pure 6NF option and you risk duplication (how do you determine which questions will not be frequent questions?), But if you really wanted to, you could do it .

There is a good question about 6NF here: I would like to understand 6NF with an example

I hope this made some sense, and I hope this helps. If you have questions, leave a comment.

Answer 2

Indeed, this is definitely a problem for which SQL is not the right solution. Forget about normalization. This is a task for NoSQL document stores. Each user as a document having some important fields, such as id, name, pwd, etc. And each group adds the ability to add some fields. Unique fields can have group-id-prefixed names, common fields (which cover a more general concept) can have this field name for free.

In addition to users (and groups), then you will have descriptions of fields with a name, type, possible values ​​... which is also very good for document storage.

If you use a document repository with key values ​​from the very beginning, you get this free opportunity to structure your data and query it (although not in SQL, but using one or another NoSQL database).

Answer 3

First, I would like to note that the following structure is the basis of your database, and you will need to expand / reduce it.

The following objects exist in the database:

• user (user only)
• group (any group)
• (list of requirements combined in a template to simplify the assignment)
• requirement (one requirement. For example: date of birth, gender, favorite sport)

"Modeling":

**User** user_id user_name **Group** name group_id user_group user_id (FK) group_id (FK) **requirement**: requirement_id requirement_name requirement_type (FK) (means the type: combo, free string, date) - should refers to dictionary) **template** template_id template_name **template_requirement** r_id (FK) t_id (FK) 

The next step is to simulate the appropriate scheme for storing constraints, i.e. validation rule for any requirements in any template. We must separate it, because for different groups the same restrictions can be different (for example: "age"). You can use the following table:

 **restrictions** group_id template_id requirement_id (should be here as template_id because the same requirement can exists in different templates and any group can consists of many templates) restriction_type (FK) (points to another dict: value, length, regexp, at_least_one_value_choosed and so on) 

So, as I said, this is the foundation. You can freely simplify this scheme (erase tables, several templates for a group). Or you can make it more general by adding the ability to create and publish temaplate, requirements, etc.

I hope you find this idea useful.

Answer 4

You can save data such as JSON or XML (Structure, Data)

User table

• Userid
• Username
• Password
• Groups -> JSON array of all groups

GroupStructure Table

• Groupid
• Groupname
• Group structure -> JSON structure (with specified fields)

GroupData strong table>

• Userid
• Groupid
• Groupdata -> JSON Data

Answer 5

I think this covers most of your limitations:

  users
 user_id, user_name, password, birth_date, gender

 1, Robert Jones, *****, 2011-11-11, M

 group
 group_id, group_name

 1, movie goers
 2, Extreme Sports

 group_membership
 user_id, group_id
 eleven
 12

 group_data
 group_data_id, group_id, group_data_name

 1, 1, Favorite Genres
 2, 2, Favorite Activities

 group_data_value
 id, group_data_id, group_data_value
 1.1, Comedy
 2.1, Sci-Fi
 3.1, Documentaries
 4.2, Extreme Cage Fighting
 5.2, Naked Extreme Bike Riding

 user_group_data
 user_id, group_id, group_data_id, group_data_value_id

 1,1,1,1
 1,1,1,2
 1,2,2,4
 1,2,2,5

Answer 6

I had similar problems. I'm not sure if this will be the best recommendation for your specific situation, but think about it.

• Provide a data storage tool in the form of XML or JSON or another format that restricts the data, but basically stores it in a field that does not have a specific format.

• Provide a way to store the definition of this data.

• Provide a lookup / index table for the data.

This is a combination of the above methods.

Essentially, you will create some kind of interface for your customers to create a “form” for what they want to keep. This form would indicate how much information they want from the user. It will also indicate which pieces of information you want to find.

Save this information in the definition table.

The definition table is then used to describe the user interface for data entry.

After entering user data, save the data (as xml or something else) in one table with a unique identifier. At the same time, another table will be populated as an index using

id where xml data was saved; the name of the field data is stored in the value of the stored field data. id of the data definition.

Now, when the search starts, there should be no problem finding information in the index table by name, value and definition identifier and returning the xml / json data identifier (or any other) that you saved in the table that the data form was saved.

This data must be transformable after its recovery.

I seriously described the details here, hope this is enough to get you started. If you want any explanation or additional information, let me know and I will be happy to help.

Answer 7

If you are not stuck in mysql, I suggest you use postgresql, which provides inline array data types.

you can define a varchar field array definition to store group-specific fields in the table of your groups. to store values ​​you can do the same in the membership table.

Compared to parsing xml types, this array will be very fast.

If you don’t like the approach to the array, you can check the xml data types and optional hstore datatype, which is a repository of key values.