Database Design: More Rows vs. More Columns?

Question

I'm developing an app and it requires me to design the database. I'm wondering what'd be more optimal design in following scenario:

Approach 1:

Have one user table with all the user fields viz.

id | uid | username | first_name | last_name | profession

OR

Approach 2:

Table I:

id | uid | username 

Table II:

uid | key | value |
1   | 'first_name' | John
2   | 'last_name'  | Donald and so on

The first approach favours more columns to store the user data, while the second approach relies on multiple tables and stores data into several rows for each user.

The second approach would mean that for each user, the user_meta table will have large number of rows while approach #1 will be more compact.

Questions:

1. Which approach is better in terms of performance and speed of queries?
2. Is there any rule for designing the database where you've to decide whether to store the data in rows vs columns?

Answer 1

The first model you propose is a regular relational design. It is widely used, very efficient in terms of speed and storage space, but it requires you to understand the data model before you store the data; adding an additional field would require a schema change.

The second model you propose is commonly known as "Entity-Attribute-Value" or EAV. You'll find a detailed question here.

It's worth thinking this through though - imagine a screen which lists all users who have logged in today. In your first model, you issue a single query - select * from users where last_logged_in >= '1 Jan 2015'

Now imagine that query in model 2 - you'd have something like

select u.*, ln.value, fn.value
from users u
outer join metadata ln on u.user_id = ln.user_id
and ln.key = 'last_name'
outer join metadata fn on u.user_id = fn.user_id
and fn.key = 'first_name'
and u.llast_logged_in >= '1 Jan 2015'

Two outer joins, and a complex query once you go beyond this trivial example.

If you have a lot of additional data, and you don't expect to use it as a major part of the relational model (i.e. use it as a criteria in a join or where statement), you can use MySQL's support for JSON or XML.

This allows you to store data whose schema you may not know at design time, and which is "sparse" (i.e. not all records have all fields populated), but it's slightly more awkward to query and populate into your client language.