shiftyroach
04-25-2003, 04:59 AM
I'll try to be as concise as I can. But I'd like to say first, I'm a longtime lurker on this forum and it's been a big help in solving most of my issues. Thanks to everyone. Right of the bat, if anyone thinks this is the wrong forum to post this in, please let me know right away and if possible direct to a place that may be of more help.
I'm developing a small concert tour archive. Its a bit more complex than I'm going to explain, but I'll reduce all parts not necessary for this issue.
Right now I've got 2 tables. 1st is tblShow. Contains showID, showDate, venue, and openingAct. All pretty much self explanitory fields.
2nd is tblSetlist. Contains songID, orderNum, encore, showID. orderNum will tell me what order that song was played in that night; encore is a boolean value, true if it was an encore song, false if it was part of the regular set; showID will tell me what record in tblShow to link to.
For the purposes of what I've got right now, to get details of a show, this works perfectly. I get all my basic info displayed, and on the left side of the page, I run query to get all records from tblSelist that have the current showID in them and arrange them descending by orderNum. Right on! :thumbsup:
Now what I want to do is eventually, is pull stats on how many times a particular song was played, and where, then link each occurance to that particular show. The database seems inefficiant already in that many song names will be repeated throughout tblSetlist. Now the only way I get this to work now, would be to run a query getting all records that have a particular song title in it, then running a query on each showID to get the date and venue it was played in. But say a song was played 500 times, thats 501 queries on a single page :eek:
I was thinking, add 2 more fields in tbSetlist. Throw in venue and showDate as well. But then that's even more redundant data. :confused: To get to the point, what's a better way to go about doing this? In terms of structure and performance, is the second option any better?
Any input would be greatly appreciated.
I'm developing a small concert tour archive. Its a bit more complex than I'm going to explain, but I'll reduce all parts not necessary for this issue.
Right now I've got 2 tables. 1st is tblShow. Contains showID, showDate, venue, and openingAct. All pretty much self explanitory fields.
2nd is tblSetlist. Contains songID, orderNum, encore, showID. orderNum will tell me what order that song was played in that night; encore is a boolean value, true if it was an encore song, false if it was part of the regular set; showID will tell me what record in tblShow to link to.
For the purposes of what I've got right now, to get details of a show, this works perfectly. I get all my basic info displayed, and on the left side of the page, I run query to get all records from tblSelist that have the current showID in them and arrange them descending by orderNum. Right on! :thumbsup:
Now what I want to do is eventually, is pull stats on how many times a particular song was played, and where, then link each occurance to that particular show. The database seems inefficiant already in that many song names will be repeated throughout tblSetlist. Now the only way I get this to work now, would be to run a query getting all records that have a particular song title in it, then running a query on each showID to get the date and venue it was played in. But say a song was played 500 times, thats 501 queries on a single page :eek:
I was thinking, add 2 more fields in tbSetlist. Throw in venue and showDate as well. But then that's even more redundant data. :confused: To get to the point, what's a better way to go about doing this? In terms of structure and performance, is the second option any better?
Any input would be greatly appreciated.