StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBest way to store 'extra' user data in MySQL?
primarykey
Id
4917864
data
AcceptedAnswerId
4917962
AnswerCount
2
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-02-07T03:32:09.787
FavoriteCount
5
LastActivityDate
2012-06-01T12:21:18.190
LastEditDate
2011-02-07T03:47:18.853
LastEditorUserId
313039
OwnerUserId
200025
ParentId
0
PostTypeId
1
Score
5
ViewCount
1863
LastEditorDisplayName
text
Body
I'm adding a new feature to my user module for my CMS and I've hit a road block... Or I guess, a fork in the road, and I wanted to get some opinions from stackoverflow before I commit to anything. Basically I want to allow admins to add new, 'extra' user fields that users can fill out on registration, edit in their profile, and/or be controlled by other modules. An example of this would be a birthday field, a lengthy description of themselves, or maybe points the user has earned on the site. Needless to say, the data stored will be varied and can range from large amounts of text, to a small integer value. To make matters worse - I want there to be the option to search this data. With that out of the way - what would be the best way to do this? Right now I'm leaning towards having a table with the following columns. <pre><code>userid, refFieldID, varchar, tinyint, smallint, int, text, date, datetime, etc. </code></pre> I would prefer this as it would make searching significantly faster, and the reference table (Which holds all of the field's data, such as the name of the field, whether it's searchable or not, etc.) can reference which column should be used when storing data for that field. The other idea, which was suggested to me and I've seen used in other solutions (vBulletin being one, although I have seen others whose names escape me at the moment), where you just have the userid, reference id, and a medtext field. I don't know enough about MySQL to say this with any certainty, but this method seems like it would be slower to search, and possibly have a larger overhead. So which method would be 'best'? Is there another method I'm missing? Whichever method I end up using, it needs to be fast to search, not massive (A tiny bit of overhead is fine), and preferably allow complex queries used against the data.
Tags
<mysql><database-design><user-accounts>
Title
Best way to store 'extra' user data in MySQL?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USSerge
UserOwnerUserId
1. USJon
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.