I am racking my brain over this, probably because I only know very simple mysql functions. Basically I''ve got a few tables, ex: Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name) I want to search through both album.name, band.name, and label.name throwing all results into a variable, with no redundant info. I think what I need to be doing is setting up some foreign key relationships. I am on the right track? Or way off? -- Posted via http://www.ruby-forum.com/.
Dick Dishkuvek wrote:> I am racking my brain over this, probably because I only know very > simple mysql functions. > > Basically I''ve got a few tables, ex: > > Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name) > > I want to search through both album.name, band.name, and label.name > throwing all results into a variable, with no redundant info. > > I think what I need to be doing is setting up some foreign key > relationships. > > I am on the right track? Or way off?Thinking over this more... Should I be separating these tables in the first place? What would the advantage be over one table that looked like this: Music (id,band,album,label), than splitting the tables like I have above? -- Posted via http://www.ruby-forum.com/.
On 4/25/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote:> > Dick Dishkuvek wrote: > > I am racking my brain over this, probably because I only know very > > simple mysql functions. > > > > Basically I''ve got a few tables, ex: > > > > Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name) > > > > I want to search through both album.name, band.name, and label.name > > throwing all results into a variable, with no redundant info. > > > > I think what I need to be doing is setting up some foreign key > > relationships. > > > > I am on the right track? Or way off? > > Thinking over this more... Should I be separating these tables in the > first place? What would the advantage be over one table that looked > like this: > Music (id,band,album,label), than splitting the tables like I have > above?no, you should keep the tables separate, to prevent redundancy. ie, if you use one table such as Music, it may look like this: Table Music: id | band | album | label 1, band1, album_a1, label1 2, band1, album_a2, label1 3, band1, album_a3, label1 4, band2, album_b1, label1 5, band2, album_b2, label2 6, band2, album_b2, label2 notice that band1 is repeated and label1 is repeated quite a few times. Also, if you wanted to change the name of label1 to "Label 1", you may have to update thousands of rows (depending upon how large your table is). If you keep them separate, the schema would look like the following: bands: (I would probably rename this table to ''artists'') id | band_name | 1, band1 2, band2 albums id | album_name | band_id 1, album_a1, 1 2, album_a2, 1 3, album_a3, 1 4, album_b1, 2 5, album_b2, 2 labels id | label_name 1, label1 2, label2 bands_labels (join table) band_id | label_id 1,1 2,1 2,2 and your models would look like: band.rb has_many :albums has_and_belongs_to_many: labels (since a band can switch from one label to another during their lifetime) album.rb belongs_to: band labels.rb has_and_belongs_to_many :bands hopefully this will help you get started. Mike -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060426/f39b81bd/attachment.html
here''s some SQL that should work: select id, name, band_id as subid, ''Album'' as source from albums union all select id, name, label_id, ''Band'' from bands union all select id, name, null, ''Label'' from labels You could stick this in a view in the database, or run it from within Rails by one of the custom sql methods... You could conceivably join this all into one basic table ala the Single Table Inheritance pattern as well... (with views to slurp out the individual subtypes if needed). You do have indexes on the Name fields in those tables, right? On 4/25/06, Dick Dishkuvek <dishkuvek@0xf00d.com> wrote:> I am racking my brain over this, probably because I only know very > simple mysql functions. > > Basically I''ve got a few tables, ex: > > Albums (id,name,band_id); Bands (id,name,label_id), and Label (id,name) > > I want to search through both album.name, band.name, and label.name > throwing all results into a variable, with no redundant info. > > I think what I need to be doing is setting up some foreign key > relationships. > > I am on the right track? Or way off? > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
all the solution provided so far used SQL, isnt there an elegant way to merge all the records using activeRecord? Or create an hash with all the names? -- Posted via http://www.ruby-forum.com/.
Mike Garey wrote:> bands_labels (join table) > band_id | label_id > 1,1 > 2,1 > 2,2 > > and your models would look like: > > band.rb > has_many :albums > has_and_belongs_to_many: labels (since a band can switch from one label > to > another during their lifetime) > > album.rb > belongs_to: band > > labels.rb > has_and_belongs_to_many :bandsThank you Mike. It looks like one of the primary items I was missing was a separate join table. When I set up inheritance in the modules like that, where does that functionality express itself when coding? Does it mean that I can liberally string together combinations of artist, album, label and attached it to a method for processing? I''ve been trying to find a resource that will explain the inheritance a little more in depth than the rails api. -- Posted via http://www.ruby-forum.com/.
Corey Lawson wrote:> here''s some SQL that should work: > > select id, name, band_id as subid, ''Album'' as source > from albums > union all > select id, name, label_id, ''Band'' > from bands > union all > select id, name, null, ''Label'' > from labels > > You could stick this in a view in the database, or run it from within > Rails by one of the custom sql methods... > > You could conceivably join this all into one basic table ala the > Single Table Inheritance pattern as well... (with views to slurp out > the individual subtypes if needed). > > You do have indexes on the Name fields in those tables, right?No, I do not have indexes on the name fields. Clearly I need to dive into the documentation at mysql.org a little more. Additionally, I do not know what a view is. -- Posted via http://www.ruby-forum.com/.