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?