Database setup question

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

That what you need?

Jason

elitegoodguy wrote:

Warning here; join tables don't generally have their own id.

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?

Michael Campbell wrote:

>

...

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.

Ah yes, you just use the

PRIMARY KEY(jobsite_id, product_id);

syntax.