If I'm reading this right, you want a many-to-many relationship between
Products and Jobsites. To do this, you'll want a simple 'join' table
like this
id int...
jobsite_id int
product_id int
which holds an entry for each product used at each site.
This table has a one-to-many relationship with each of the other
tables.
For your gold lamp query, do
SELECT *
FROM josites j
INNER JOIN products_jobsites p_j
ON j.id = p_j.jobsite_id
INNER JOIN products p
ON p_j.product_id = p.id
WHERE p.name like 'Gold Lamp';
I have a slightly different need: I want to create tables recording how
network devices are linked. i.e. devA is connected to devB,
devA is connected to devC,
devD is connected to devE
devE is connected to devF
etc
I'm thinking I need two tables:
devices
- id int
- name string
links
- id int
- dev1_id int
- dev2_id int
My problem is I'm not sure how to model this in rails. Would
acts_as_list or acts_like_tree work here?
Warning here; join tables don't generally have their own id.
I fact I created a join table with an id column once. It worked ...
sort of. Some db entries it would create, others it wouldn't. Drove me
nuts until I went back and read that join tables <b>should</b> not have
a primary id field. Worked much better once I deleted the primary id
column.
