> 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
<snip> This sounds like a great problem for a database design class. Give the students a couple of constraints, and have them build the design. Then give them some more info, and have them make mods. Rinse, repeat... Similar to the directory problem, sometimes you need to convert a type of data into a more abstract element. For example, people have telephone numbers, web addresses, and email addresses. Telephone numbers can be land, fax, cell, car, boat, switchboard, direct lines, and so on. Same for websites, say, hentzenwerke.com as well as whilhentzen.com. And email addresses, [EMAIL PROTECTED] vs [EMAIL PROTECTED] So one solution is to have a 'coordinates' table that contains all that info, together with a tag identifying what type of coordinate it is: 414.555.1212 voice 414.555.1214 fax 414.555.1200 cell [EMAIL PROTECTED] email dibblefamily.com web and so on. Similarly, you have people entities, but a 'people' can be a person, a function (company sales manager) or a group (eby family). Yes, it is all good clean fun, until someone loses an eye. Then it's good clean one-eyed fun. Whil > 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 > > > [excessive quoting removed by server] _______________________________________________ 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.
