Rails - Aug 2006 - many-to-one relationship, do I need a second table?

Ok, to keep things short. Im wondering if I need a secondary 
relationship table to handle my many-to-one relationships. Here is an 
example of what I''ve written down. For instance say I want to find all 
of the people in a given location.

class Location < AR:Base
  has_many :people
end

class Person < AR:Base
  belongs_to :location
end

My SQL tables look like:

CREATE TABLE `locations` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  created_at DATETIME,
  updated_at DATETIME,
  PRIMARY KEY (id)
) TYPE=''InnoDB'';

CREATE TABLE `people` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  created_at DATETIME,
  updated_at DATETIME,
  location_id INT UNSIGNED,
  FOREIGN KEY (location_id) REFERENCES locations(id),
  PRIMARY KEY (id)
) TYPE=''InnoDB'';


And so, to describe my question, Im wondering if I need the secondary 
relationship table (like a has_and_belong_to_many relationship) in order 
to search for people by location.

CREATE TABLE `locations_people` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  location_id INT UNSIGNED,
  person_id INT UNSIGNED,
  FOREIGN KEY (location_id) REFERENCES locations(id),
  FOREIGN KEY (person_id) REFERENCES people(id),
  PRIMARY KEY (id)
) TYPE=''InnoDB'';

-- 
Posted via http://www.ruby-forum.com/.

Jonathon Marshall wrote:
> Ok, to keep things short. Im wondering if I need a secondary 
> relationship table to handle my many-to-one relationships. Here is an 
> example of what I''ve written down. For instance say I want to find
all
> of the people in a given location.
> 
> class Location < AR:Base
>   has_many :people
> end
> 
> class Person < AR:Base
>   belongs_to :location
> end
> 
> My SQL tables look like:
> 
> CREATE TABLE `locations` (
>   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
>   name VARCHAR(255),
>   created_at DATETIME,
>   updated_at DATETIME,
>   PRIMARY KEY (id)
> ) TYPE=''InnoDB'';
> 
> CREATE TABLE `people` (
>   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
>   name VARCHAR(255),
>   created_at DATETIME,
>   updated_at DATETIME,
>   location_id INT UNSIGNED,
>   FOREIGN KEY (location_id) REFERENCES locations(id),
>   PRIMARY KEY (id)
> ) TYPE=''InnoDB'';
> 
> 
> And so, to describe my question, Im wondering if I need the secondary 
> relationship table (like a has_and_belong_to_many relationship) in order 
> to search for people by location.
> 
> CREATE TABLE `locations_people` (
>   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
>   location_id INT UNSIGNED,
>   person_id INT UNSIGNED,
>   FOREIGN KEY (location_id) REFERENCES locations(id),
>   FOREIGN KEY (person_id) REFERENCES people(id),
>   PRIMARY KEY (id)
> ) TYPE=''InnoDB'';
No, you don''t need a join table for a one-to-many relationship.  The 
Person and Location models and their tables are all you need.

--
Josh Susser
http://blog.hasmanythrough.com

-- 
Posted via http://www.ruby-forum.com/.

> No, you don''t need a join table for a one-to-many relationship. 
The
> Person and Location models and their tables are all you need.
> 
> --
> Josh Susser
> http://blog.hasmanythrough.com
Awesome, I figured as much, but I wanted to make absolutely sure. 
Thanks!

-- 
Posted via http://www.ruby-forum.com/.