0

I recently discovered that you can add columns to a table in Rails by doing something like:

rails generate migration add_lastname_to_users lastname:string

Previously I used to join tables which was a very complicated to me but, adding a column seems to accomplish the same task.

Why should I choose one over another?

Sebastián Palma
  • 32,692
  • 6
  • 40
  • 59
craftdeer
  • 985
  • 5
  • 20
  • 36

3 Answers3

2

A table represents a single "entity". In this case, it probably makes most sense to store the users.lastname in the same table.

On the other hand, suppose a user can have many phone numbers. In this case, it is better to normalise the database and store this data in a separate table.

In other words, you want to avoid doing something like this:

users.phone_number_1
users.phone_number_2
users.phone_number_3

The key issues with this approach (as explained in more detail by the above link) are:

  • You'll have lots of redundant columns, for must users. This causes wasted storage space, and decreased performance.
  • You need to keep adding new columns if a user goes over the limit (e.g. 3 numbers, because there are 3 columns).
  • Querying the data gets much harder. For example, suppose you want to query "all users who have phone number X" -- you now need to search across multiple columns!

Instead, create a separate phones table - which is joined the the user by a user_id column.

Tom Lord
  • 27,404
  • 4
  • 50
  • 77
  • awesome. ty guys. It never came across my mind that someone could have multiple phone numbers and may need more entries. – craftdeer Jul 13 '17 at 21:46
  • My `users.phone_numer` example won't be true in all systems; there's a reason why your question was put on hold as "opinion based". It's totally plausible that your system *will* only store one phone number for each user - in which case, it may be fine to store that value in the same table! – Tom Lord Jul 14 '17 at 08:36
  • ...But maybe you also want to store more attributes of the `phone_number` - e.g. the "type" (mobile/landline), the "country code", a "disabled" flag, a "last_contacted_at" date, ..... in which case a separate table is more appropriate. – Tom Lord Jul 14 '17 at 08:38
1

I guess it depends on your application, but generally it's best to "normalize" your database. That is, define individual tables for specific objects. A user table might have the fields user_id, first_name, & last_name. You can then join on the user_id field. This tends to make your lookups faster and your tables smaller.

https://en.wikipedia.org/wiki/Database_normalization

zfisher
  • 61
  • 6
0

This is not so much related to rails and ActiveRecord but more a question of database design.
Without going into too much detail: In a relational database management system you join tables (or columns in your case?) when some piece of information you need is already available in a different place (usually another table) (hence the "relational" in the name, tables are related to each other and share information). You do not want to repeat data.
This is different in NoSQL databases where joins might not even exist (MongoDB has the notion of embedded documents, you will at some point have to repeat data)
I your case, it is easy (from the point of view of the DB) to just take the already available information (first_name + last_name) and return that. Adding another column with the same information seems 'wasteful'.
You should be able to define a helper method in your model that returns the full name, see create a name helper

Marcus Havranek
  • 126
  • 4