CODEWITHSUNDEEP
ruby-on-railsdatabase-designmodel
Hommer Smith
Hommer Smith

Reputation: 27862

How to store City and Neighborhood associated with a Restaurant?

I have a list of Restaurants. Each one is located in a neighborhood/district, which is in a particular City.

How would I relate Restaurants with both neighborhood and city? Would I want to do:

Restaurant (belongs_to) -> Neighborhood
Restaurant (belongs_to) -> City

or

Restaurant (belongs_to) -> Neighborhood
Neighborhood (belongs_to) -> City

What would be the advantage or disadvantage of taking one or the other approach, and what should I choose?

Thanks

Upvotes: 2

Views: 203

Answers (3)

Dan Reedy
Dan Reedy

Reputation: 1857

The Relationships

The second set of relationships would be the most appropriate. The primary reason, as Mik_Die mentioned, is that it is normalized. If you were to look at your DB schema for the first example you would have something like the following

Restaurant (belongs_to) -> Neighborhood
Restaurant (belongs_to) -> City

Table: Restaurant
Column          |  Type       | 
---------------------------------------------
ID              |  Integer    |  Primary Key
name            |  String     |
neighborhood_id |  Integer    |  Foreign Key
city_id*        |  Integer    |  Foreign Key

Table: Neighborhood 
Column          |  Type       | 
---------------------------------------------
ID              |  Integer    |  Primary Key
name            |  String     |
city_id*        |  Integer    |  Foreign Key

Table: City 
Column          |  Type       | 
---------------------------------------------
ID              |  Integer    |  Primary Key
name            |  String     |

If you look at the columns I put an asterisk beside you will see that it is duplicated in two different tables, something you want to avoid while normalizing your database.

The second schema is going to be nearly identical. You would simply remove the city_id column from Restaurant.

Restaurant (belongs_to) -> Neighborhood
Neighborhood (belongs_to) -> City

Table: Restaurant
Column          |  Type       | 
---------------------------------------------
ID              |  Integer    |  Primary Key
name            |  String     |
neighborhood_id |  Integer    |  Foreign Key

Where Rails Comes In

Your post was tagged Ruby on Rails, so I think it is important to discuss how Rails views this relationship. You are familiar with the belongs_to and has_many associations. Rails provides an excellent extension to has_many with the :through option.

I'm going to assume you were interested in storing the City in the Restaurant table since you want to be able to find all the restaurants that belong to a whole city. The :through option of has_many allows for that functionality.

Your models would look something like this

class Restaurant < ActiveRecord::Base
  belongs_to :neighborhood
end

class Neighborhood < ActiveRecord::Base
  has_many :restaurants
  belongs_to :city
end

class City < ActiveRecord::Base
  has_many :neighborhoods
  has_many :restaurants, through: :neighborhoods
end

You could then do something like this

@neighborhood.restaurants # => Returns all restaurants for that neighborhood
@city.restaurants # => Returns all restaurants from each of the neighborhoods belonging to the city

Upvotes: 3

Aditya Kapoor
Aditya Kapoor

Reputation: 1570

The second version is more better than first one because you would have to keep record of associations only once. In first case, you are redundantly trying to save the city and neighborhood for a restaurant which is not needed at all...

Upvotes: 0

mikdiet
mikdiet

Reputation: 10018

In SQL databases you should normalize your data, so second variant is more appropriate.

Upvotes: 0

Related Questions