I'm looking for suggestions on a general approach towards database design. I'm wondering what's the most optimal design: Having more rows or adding columns to store data.
For example. Let's say I wish to store user data. I've two approaches:
Approach I:
Table I 'user' -
| id | name |
| 1 | John |
| 2 | Peter |
....
.......
Table II 'user_meta' -
id | user_id | key | value |
1 | 1 | country | India
2 | 4 | country | Australia
3 | 4 | city | New York
Approach II -
| id | name | city | state | Country |
| 2 | Don | Redwood City | California | United States
What would be your choice and why? Is there any general rule about choosing rows or columns while defining the database? I look forward to your responses. Thank you very much in advance.
My opinion is approach I. It will save your space. It will help you make relational db and way better approach to save space.
Imagine that, you are following approach II where you have 1 M user where they don't have any city | state | Country . Meaning, in your database 1 M empty space. Isn't that wasting?
Same case you can look into approach I, where you will only add record into user_meta where a user has data. So, there is no ways to have empty data in this approach.
@tisuchi - Does it really waste space? I was thinking that in the second approach, I'm adding a large number of rows to store the data that could be put directly in one row. My guess is that it'd be easier for the database server to fetch records directly from a row than looking up for it in two tables.
Also, what if the meta information is mandatory? Would approach II be better?
Well, its depends. The opinion that I gave you, its my personal opinion only.
Yes, you can follow your approach if you make it mandatory.
But what's my concern is, you should not create more and more column in your table. The best approach, I always follow to create relational table instead of adding more and more columns.
I want to add another option as well that you might not have considered. If you're using MySQL 5.7+ or something with relative feature parity, you might consider using a json column to store settings. In general I shy away from json columns because I want the relational data, not a document store (necessarily), but I will say I've found json columns excel at storing user metadata and similar use cases. Data you might not need to frequently query, but maybe stores user settings or options or the like.
