Re: variation of "weak link" lookup table

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

so i would have something like

table Person
personid

table RecordingAs
persionid
name

i'm wondering if i might make my life easier as far as integrating with
maypole by also doing the following:

table TrackPerson
trackpersonid
trackid
personid (FK to RecordingAs.personid)

so that TrackPerson has a single column PK.

steve



On Thu, 2004-06-24 at 08:10, Randal L. Schwartz wrote:
> >>>>> "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.