[thelist] SQL View: Join? SubQuery?

[Susan Wallace]

Hi Sean,

This is actually just a small piece of a larger system that needs a band
aid (long story). I think I see what you mean here, but I'm a bit confused.
I was planning to store the complete program information in a View, and
then query the View, not the individual tables (make sense?). Is that not
as effective?

Thanks for your help!

Susan


>A little rearrangement of your tables will make your querying much easier.
>Fields of lists may seem convenient at first but will end causing a lot of
>problems and ugly code.  Right now you want a list of compositions for each
>program, but what happens when you want a list of programs featuring a
>particular composition?  Plus, WHERE X in (list) is a lot more work for your
>server than WHERE X = Y.
>
>So try something like;
>
>Compositions
>         ID
>         ComposerName
>         Title
>
>Programs
>         ID
>         Venue
>         Date
>         Conductor
>
>PerformedPieces
>         ID
>         ProgramID
>         CompositionID