Re: variation of "weak link" lookup table

[prev] [thread] [next] [Date index for 2004/06/24]

it's totally possible that i'm wrong, but...

i'm dealing with a situation where the artist names are totally 
arbitrary.  here's a situation that's typical of my data.

we have artist names a, b, c, and d, which all refer to the same person 
who has actually done the recording, and who often has a real name no 
one knows or cares about.  what i want is that whenever i search for b, 
i want all the tracks done by a, c, and d as well.  and if i search for 
c, i want a, b, and d as well.  there can be an arbitrary number of 
artists associated with each other.  for fun, we'll have unrelated in 
any way artists e, g, h.  (does this remind anyone of the LSAT?)

track    |  artist name used for track
track1  |  a, e
track2  |  b, f
track3  |  c
track4  |  d, g

i might arrive at these tracks through numerous pathways, and it 
behooves me to have as many of them available as possible since mystery 
artist aliases can make it hard for people to know how many tracks a 
certain person they are into has actually done.  since i don't know 
their "entry point" into the world of artist x who is known by a, b, c, 
and d, using the 2 column strategy guarantees i'll find the other bits, 
however many.

a dba i work with suggested this solution.  but it's still possible 
that it's not the best way.  he warned me about the hassle.  maybe it 
will be incentive to learn to use the trigger features of CDBI!  any 
thoughts on my strategy are appreciated.

as for the other item:

>> Artist->has_many(aliases => ['ArtistAlias' => 'artistid1']);
>
> I'm not sure that that last line is correct. As you've multiple columns
> pointing to the same table in ArtistAlias you're going to need the 3rd
> argument to has_many. I would have thought that you'll want:
>
>  Artist->has_many(aliases => ['ArtistAlias' => 'artistid2'], 
> 'artistid1');

Artist->has_many(aliases => 'ArtistAlias', 'artistid1') actually did 
the trick!  now it works like i wanted, as a coherent Artist object.  
now i can do this:

my $aliases = $artist->aliases;
while( my $alias = $aliases->next) {
     print $alias->artistid2->name, "\n";
}

thanks so much
steve

On Jun 24, 2004, at 12:20 AM, Tony Bowden wrote:

> On Wed, Jun 23, 2004 at 08:47:05PM -0500, step2@xxxxxxxxxxxxxxxx.xxx 
> wrote:
>> and i have artist alias setup like so:
>> artistid1 | artistid2
>>      1    |     4
>>      4    |     1
>
> Do you need to have this database structure? This sort of thing is 
> usually
> easier solved if this table doesn't work both ways - i.e. there's a
> "deinitive" version of the artist which is only ever in the first 
> column
> of this, and all the aliases are in the second column...
>
> In you're set-up you're duplicating information. If you later 
> discovered
> that you'd accidentally linked artist 1 and artist 4 you'd have to
> delete two rows from your table, rather than just 1 - an anathema to
> most database designers!
>
> Tony