Re: Counting links between to records on the same table (Clustering?)

Dale Walker wrote:
> I'm trying to develop a little method that groups DJs together into
> similar tastes in music so that people can work out if they might like
> a certain night out.

finally, a real application, just kidding.

>
> Now in the DJ world, no-one can agree on definitions of musics styles,
> some have many music styles, some have styles with no names yet, etc.,

same as this group, haha.

> so whilst having a separate field containing a one-many link to a list
> of DJstyles might be useful some of the time, it's not going to work
> every time.
>
> So I thought that using my quite extensive database of previous
> activities of DJs who play within the London area I could perhaps use
> some method of totalling up the times when two DJs appeared together
> on the same night and therefore be able to work out some sort of
> similarity that way.
>
> My first attempt at this left me with a problem.
>
> I've got a table of DJs names with various other bits and bobs about
> them. I tried to create a second table with a couple of fields, the
> first being that ID of a DJ, the second being the ID of another DJ on
> the same night. A third field would simply be a counter to add up each
> time the two people played together. The problem was, there was no way
> of automatically making sure that if the two DJs were entered in
> reverse the totals would match.
>
> How do I make sure that regardless of which order the two DJs names
> are entered, the total number of times they played together will be
> correct and match?
>
> I had the thought that I could somehow create a list of all the
> possible pairings and then use the id from that to link to each Name
> on the master DJ Name list but I'm unsure of how to do that. I'm
> trying to avoid as much coding as possible but realise that sometimes,
> coding is the only way out.
>
> If I'm barking up the wrong tree, please let me know.
>
> I also kind of get the feeling I'm getting into the area of clustering
> algorithms. Are there any resources on the net that deal with database
> implementations of that?
>

i suppose the canonical approach is to say that the relation is between events and dj's, not among dj's, so a composite key could be event id and dj id where the implicit total is always 1! or 1 factorial if you prefer. undoubtedly somebody will suggest constraints on the grounds that this key is not mapped well by conventional products but this is a theory group so that doesn't matter, haha.

so my cut at it is that this is a query question, not a design question and i'll leave the sql to people who like that stuff.

i've given you perhaps no answer at all. it's just that it makes me wonder - i don't know about others but i often find myself wanting to impose some kind of geometry on such designs even though the rm doesn't intend that. personally, i'd like to see a product that lets me say two unpopulated 'views' or relations for that matter, are somehow equivalent without a bunch of obscure constraint coding. i don't know if such a concept has a name, perhaps co-dependent relations where the db asserts that you can't have one without the other or if it's all part of the user or psychological aspect of the rm or if it's not a concept at all and just logically ridiculous.

p

>
> ---
> Dale Walker
> London Techno Events
> dale_at_sorted,org
> http://london.sorted.org
Received on Mon Jun 27 2005 - 17:34:58 CEST