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

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 …

Jump to Post

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 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.