Searching over multiple MySQL tables

Rails
1

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?

2

Dick D. 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?

3

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?

4

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?

5

On 4/25/06, Dick D. [email protected] wrote:

throwing all results into a variable, with no redundant info.
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

6

Mike G. 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 :bands

Thank 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.

7

Corey L. 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.