I'd take the array out of the lineup, and replace it with the a master-detail structure.
Brainstorm mode (read it all to get the big picture):
Lineup would contain the ID of the band, the id of the performer, a date and the instrument he played at that point in time.
If you want to get the lineup for band 5 on dec-1-2000, you can just do something like.
SELECT *
FROM lineup
WHERE bandid=5
AND date=dec-1-2000
But performers change all the time, not just when they release albums, and you don't want to waster your time entering new records for performers that did not change bands or instruments.
So we need to know wether the linup data is still valid, we add an end date
The enddate will contain the date on which the old data became invalid (because new data arrived)
If no new data has arrived yet, and the 'old' data is still valid, just leave the enddate field blank (NULL, so make sure you don't define the field as 'NOT NULL')
Now you can get the lineup like this:
SELECT *
FROM lineup
WHERE bandid=5
AND startdate>dec-1-2000
AND enddate IS NULL
One thing to remember is that when you insert a new record, you MUST update the old record to set it's enddate!
You can get all sorts of funky data fromthis setup....
if you want all the instruments a performer played over time:
SELECT instrument
FROM lineup
WHERE performerid=X
ORDER BY date ASC
Or the number of people who played in the band over time:
SELECT count(*)
FROM lineup
WHERE bandid=5
GROUP BY startdate ASC
Did that make sense of did I just jet right over your head at mach 4? (I have a tendency to do that sometimes)
