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? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Jack720 wrote:> 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? > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On Sep 30, 9:18 pm, William Pratt <bi...-YbheRAKfYF4eIZ0/mPfg9Q@public.gmane.org> wrote:> 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/UnderstandingPolymorphicAssoc... > > I hope this helps. > > -Bill > > > > Jack720 wrote: > > 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?- Hide quoted text - > > - Show quoted text ---~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---