I'm starting on a new project renewing a music festival website. On balance I decided that it could do with being dynamic to facilitate updates. However, while the schema for the festival is not too bad, I'm a little uncertain about the querying strategy for displaying the festivals on a page.
General info on the schema:
Festival table: id(INT), name (VARCHAR), description (VARCHAR), start(DATE)
Day table: id(INT), festival_id(INT), day(INT)
performance table: id(INT), composition_id(INT), day_id(INT), order(INT)
artist_lkup table: id(INT), name, bio, composer (BOOL), performer (BOOL)
instrument_lkup: id(INT), name(CHAR)
performance_artist_fact table: performance_id, artist_id
composition_lkup table: id(INT), name(CHAR), description(VARCHAR), composer_artist_id(INT)
arrangement_lkup table: id(INT), composition_id(INT), composer_artist_id(INT)
arrangement table: id(INT), description(VARCHAR), composition_id(INT), arranger_artist_id(INT), original_arrangement (BOOL)
In the future, there will be a requirement to look up artist and composer biographies, but the immediate problem is using this data and displaying it as a festival programme with the daily programs and the performers etc. I've created this schema myself, so if you have any observations on improvements then they'll be very welcome.
In the past, I've done simpler PHP/MySQL projects, but I can already see that this has the potential to get pretty gutty. Would people recommend a framework (if so which) for this or to build my own classes? I have actually been toying with the idea of using Ruby for this project. You can assume that any learning I need to do will be useful in the future so that's not a barrier.
There's a lot of questions here I know. Your feedback is greatly appreciated.