From reading what's already been posted, I'd say that we're all more or less on the same sheet of music. Given that, I'd say our first step would probably be to design a system to store the data we are going to gather. Once that's complete (or at least in a useable form), then we can begin to work on ways to put stuff in it and take stuff out of it.
Arachnid uses a sort of project agnostic schema. It contains more pre-calculated data than we are probably interested in (this makes it fast, but expensive in terms of CPU time and storage space), but it may make a good basis for a lighter design.
In Arachnid, each project gets several tables:
Team Index: This gives each team a unique ID, and contains descriptive stuff about the team.
Team Entry: This is the big table for holding team data. It contains a great deal of pre-calculated data and is basically the data archive. For each update (by timestamp) there is 1 entry for each team that exists at that udpate.
Team Current: Contains only the most recent update for each team. This gives us a small fast table to access when we don't need to pull history.
User Index: This works just like the team index, giving each user a unique id, as well as containing their team affiliation, status, etc.
User Entry: This works just like the team data table. It's an archive full of pre-calculated data.
User Current: This is a table that always contains only latest update data for each user. It's designed to stay small and is used by display scripts when only the most recent data is needed. This allows for fast access by the front end.
Movement: This table tracks user movement from team to team.
SystemState: Currently contains the timestamp of the most recently completed update.
Project Entry: For projects that I'm tracking in their entirety (i.e. DF), this contains the project wide data in roughly the same format as the Team Entry and User Entry tables.
You can grab a version of the SQL used to create these tables right here.
You'll notice that there is an awful lot of pre-calculated info in these tables. I think most of that needs to go. You'll also see that there is some redundant data in there that also needs to be dealt with.
One of the things that *has* worked pretty well is allowing 2 data types for every project. Most projects have two, some have more, some have less. Two, however, seems like a pretty good compromise. For projects that don't have two, you just insert a NULL into the other. For projects with more than two, you can generally get away with ignoring all but the two most important. For example, in DF we have points and structures.
Any thoughts or comments? I'll probably sit down and build a slimmer table structure for everyone's perusal later tonight...