Re: variation of "weak link" lookup table

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

>>>>> "steve" == steve shapero <step2@xxxxxxxxxxxxxxxx.xxx> writes:

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

Sounds like you have:

Person <-- 1:n -->> RecordingAs <-- 1:n -->> Track <<-- n:1 --> Album

So you need a Unique Person ID, then list the names by which
they recorded the tracks.  Then you merely have to pop up to the
"Person" record to get all Tracks for a Person (indirecting
through the RecordingAs).

RecordingAs will be a very light record... PK of Name (text),
FK of Person.id.

You might need to look at how the IMDB does it... they have many
actors with slightly varying names, and some names that collide that
are really different people.

        -- 
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@xxxxxxxxxx.xxx> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!