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’;

Jonathon M. 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 S.
http://blog.hasmanythrough.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 S.
http://blog.hasmanythrough.com

Awesome, I figured as much, but I wanted to make absolutely sure.
Thanks!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs