Alright, so I am embarking on a fairly large project that is complete with user profiles. My question is, what's the fastest, most efficient way to store user meta like books, movies, etc.
I was planning:
metaid | userid | firstname | lastname | movies | books | biography | favfood
Now those aren't necessarily my categories, but would that be efficient? One row per user containing all of his/her data?
OR should I do it the Wordpress way of

metaid | userid | meta_key | meta_value
   1   |    34  | movies   | The Godfather, Sleeper, Ironman

So each user could have potentially many rows in a database.
So basically is it more efficient to have user data on one row and multiple columns or on many rows and few columns? or does it not matter?

Recommended Answers

All 2 Replies

What does "fairly large" mean? 1.000 users, 10.000, a million? If it's below a million you should care primarily for coding and usage efficiency, not for performance. MySQL is quite performant itself. Avoid the wildcard * in your queries, though.
So it boils down to a question of standard table design. If each user may have exactly zero or one movie, book etc. and you have a limited number of categories, those categories belong in the user table. If the category number may be variable you should store the values in a separate table. But then you have to make sure (e.g. with a unique index) that each user has only one entry in each category.
If a user may have more than one value in a category then you definitely need a separate table for the category values. Your example of several values in one field is bad design.

Alright, the site I am developing is potentially about a million users. Thanks for the help sir (or ma'am).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.