At 03:57 PM 3/6/07, you wrote: >On 3/6/07, Ken Dibble <[EMAIL PROTECTED]> wrote: > > I suspect you have a subtle data design problem, one iirc Tamar once > presented as "parents of unrelated siblings" - your Perfs table has an id > that you are >joining both to a PK in the Person table and a PK in the Bands table, >so one of them is wrong and irrelevant. In addition, you're joining >songs to perfs to persons and bands, and I can only suspect that in >some cases you would get multiple results for the grandchildren in >that arrangement. It's a bit hard to sort out without the data.
Hm...I'm going to try to think that through. And I should also get Tamar's book, I think. >I'd suspect it might be simpler to reconsider your design. The queries >might be a lot easier if you consider a solo performer as a band of >one, so all music goes through a group, but that might be a problem >for some of your other tables and relationships. Yeah. The idea is to have People in the system only once, since the same person can be an Artist (designated as such for a Performance, like when you go to see Bonnie Raitt--she's got a Band too, but it's not "The Bonnie Raitt Band", it's just "Bonnie Raitt"), a Performer (as when members of Tom Petty and the Heartbreakers played with Johnny Cash), or an Author (Carole King wrote "Up on the Roof" with her husband, but she didn't do the hit release by The Drifters--though her demo of the song has since been released). So "Artist" isn't even a table in the database; it doesn't have any meaning except in the context of a particular Performance, which I have defined as a particular song performed by a particular Artist (which may be a person or a Band; at least, I'll consider groups of named people, such as Peter, Paul & Mary, as a Band, though they're also likely to get into the database as People too, since they've all done solo work) on a particular date. And there's the musRecs table. These are Recordings--because there can be multiple recordings of the same performance, as in the case of remixes and re-releases. And that's only four tables. There are lots more that keep track of other aspects, like what albums recordings are part of; what homemade mix tapes they've been put on; the changing personnel rosters of Bands; record labels, etc. I've spent a lot of time trying to work through these issues. The more I think about the problem domain, the more complex I can make it in my own head. <g> > > If I'm understanding you > > right, they weren't extra records (like one gets, for example, when one > > doesn't use DISTINCT in some situations). The query failed to find data > > that it should have found. And it wasn't due to truncation because I tried > > it with a PADR() wrap and got the same results. Am I making sense? > >Yep. I've stared at your original SQL for quite some time, and I have >trouble wrapping my head over exactly what it would do under some >circumstances. Did I mention this stuff is hard? Yup. It's a good thing that it's also fun, then, isn't it? Ken www.stic-cil.org _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
