Can I kind of abstract the question a bit, now that I've got two or three schemes which will all fit the purpose, not to mention the SELECT syntax as well?
My original question is essentially "I know how to do two, but I don't know how to do greater than two".
My knowledge of normalisation came from a good article on, I think, O'Reilly somewhere, which was based on musical performances. Rather than having a database which says "Paul McCartney played on 'Hey Jude'", "Paul McCartney played on 'Ebony and Ivory'", you have a table where 77 = Paul McCartney, 88='Hey Jude' and 99='Ebony and Ivory' and a "played_on" table which maps 77 to both 88 and 99.
So that makes sense in terms of normalisation.
But it's a binary system. There are only two states of Paul McCartney as regards played_on: he played on 'Hey Jude', but he didn't play on 'Smells Like Teen Spirit'.
What I've got with this more complex data, however, is a more-than-binary system. It's the equivalent of "On 'Hey Jude' he played piano and bass and sang, but on 'I Saw Her Standing There' he played bass and sang, and on 'Helter Skelter' he only played bass".
You can normalise that only as far as a three-column table, right?
On song A, performer B performed function C;
On song B, performer B performed function D;
On song C, performer A performed function Z;
The only way you can further normalise it would be to have a table of relationships:
In song A, you find relationship K;
In song B, you find relationship L;
In song C, you find relationship M;
And in your table of relationships, you find that relationship K == "performer B performing function C" and so on.
At this point, we're moving into areas where we've normalised to the fullest extent, but the SELECT statements are becoming terribly complex -- at what point do I stop and say "normalised is good, but this level is requiring very complicated selects and adding to the number of database hits"?
And is that last scheme actually more efficient, just because it's got down to two columns in each table? If I have a table of functions and a table of people and a table of songs and a three-column table with all the mappings, isn't that more efficient?
P.S. It's Buffy, as people who know me have probably guessed. There's Buffy, there's Faith-In-Buffy's-Body, there's Sex-Toy-Robot-Buffy, there's The-First-Evil-Impersonating-Buffy, all played by SMG of course, but there's also Buffy-as-a-child, in a couple of shows, played by other people. And once, turned-into-a-rat-Buffy played by, well, a rat. And the person who needs most normalisation is the creator of Buffy, Joss, who has been writer, director, producer, actor and composer in his time.
($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss') =~y~b-v~a-z~s; print
In reply to Re: Re: 2Re: Normalisation Question (OT but trust me, perl implementation)
by Cody Pendant
in thread Normalisation Question (OT but trust me, perl implementation)
by Cody Pendant
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |