Hiya all. I'm new to this forum, so if this question is insanely basic, I apologize. I'm a self taught mysql user by necessity/curiosity, but I've never read a book on efficient/good database design. I've just cobbled together database driven web applications that work well but are never under any real high load, so efficiency just hasn't been a concern. MySQL has been the platform of choice.
A website I've developed keeps increasing in popularity, though, so I'd like to run my design by some more seasoned folks and see if you think it's reasonable. The key component I'm curious about is the concert listings on my site, which are stored in tables as follows:
table: users (contains UserID and metadata about user such as name, password, etc)
table: bands (contains BandID and various metadata about bands)
table: band_images (contains ImageID, BandID, displayorder and location of picture)
table: band_mp3s (contains MP3ID, BandID, displayorder and location of mp3)
table: genres (contains GenreID and Genre Title)
table: band_genres (contains BandID and GenreID)
table venues: (contains VenueID and metadata about venues)
table: shows (contains EventID, VenueID and metadata about event such as time, date, price, etc)
table: shows_bands (contains EventID, BandID, billingorder - which goes 1 to n for however many bands are playing a show)
None of these tables contains a huge number of columns, so I'm not particularly worried about the structure of a given table. My real concern is that my shows list allows for some pretty robust searching and data display, and I don't know if this is the best way to structure the database or not.
Also: What's more efficient here? First selecting shows and shows_venues, and then selecting shows_bands, bands, band_images and band_mp3s for each EventID? Or joining all of the tables at once? It seems intuitive that the huge join is vastly more efficient since there's way less queries and database connections -- what about a subselect? Is that any more efficient than separate selects?
Sadly, I've been doing mysql web programming for years, and never bothered to read up on efficiency etc because I just haven't had to. If I can quickly code something in a few hours and it works well for its needs, why bother? Now I just want to make sure I'm approaching things reasonably -- and if anyone has any recommendations on books, I'm all ears!