Table linking questions

I’m about to try coding a quick test app as my first rails test.

My first question should be quick. how do I include two ids in the
same table. for example relating two people together.

people


id
name

relationships


id
person_id
person_id (what would this be called)
relationship_type

My second question is probably more of a general DB design question
but I wasn’t sure if there was an effecient ruby way of doing this.

Lets say I have 3 tables:

people


id
name

products


id
name
cost

notes


id
detail

I’d like people and products to both be able to have multiple notes.
The app I want to build is going to have a lot of these types of
relationships and I wanted to know the best way to tie them together.
I have a few ideas but nothing I’m too hot about.

Option 1 (best?)
a seperate objects table with a unique id that is shared by all tables
involved.

objects


id (unique in people and products, only one record in any table
anywhere)
type (person, product, note)
timestamp(s)

people


id
object_id
name

products


id
object_id
name
cost

notes


id
object_id
object_id_link ? (tie to products or person)
detail

Option 2
I could add fields in notes for each table I want to link to.

people


id
name

products


id
name
cost

notes


id
person_id (only person or product would be populated but not both)
product_id
detail

Option 3
I could create a seperate table for each relationship

people


id
name

products


id
name
cost

notes


id
detail

people_notes_associations


id
person_id
note_id

products_notes_associations


id
product_id
note_id

The problem with options 2 and 3 is that while they would be OK for 2
tables if I have 4 or 5 it gets messy and cumbersom. ideas?

For your first question, the second person_id could be called parent_id,
stating that that person is the parent of the parent / child
relationship. For your second question, you should look into polymorphic
relationships. You can read more about them here:
http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations

I hope this helps.

-Bill

actually on the first question it couldn’t be parent_id because the
relationship would depend on the type. sometimes a parent/child
sometimes a spouse/child or maybe an adoption. etc. I thought rails
picked up relationships based on convention so i wanted to tie both
fields to the same table. I guess I wanted to know the convention for
that.

I’ll look into polymorphic relationships
Thank you.

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