Database setup question

Rails
1

First I want to say that I posted a question a while back on linking two
tables together and I got quite a few responses all of which were
extremely helpful. Thank you the entire forum as you’ve been a big help
with everything I’ve learned so far.

I do have one more question though.

I Am trying to build a product database with each of our jobsites. I
may have a construction job that will use a certain type of lamp (which
will be in that database) And I want to be able to assign that lamp to
another job as well, so we have more than one job using the same type of
lamp.

How do I go about assigning multiple jobsites to a database entry? The
point of this application is that I want to see all the jobs that have
used a gold lamp.

Thanks
Chris

2

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

3

On 9/18/06, jnorris [email protected] wrote:

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

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

–
More software projects have gone awry for lack of calendar time than
for all other causes combined. – Fred Brooks

4

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?

5

Ah yes, you just use the

PRIMARY KEY(jobsite_id, product_id);

syntax.

6

Michael C. wrote:

On 9/18/06, jnorris [email protected] 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 should not have
a primary id field. Worked much better once I deleted the primary id
column.