Best way to keep user history

I am creating a room booking system. For the sake of this questi开发者_StackOverflow社区on, I have two tables: user and group. They are linked in Doctrine2 via a many to many relation (so technically I have 3 tables).

Each week, each group is allowed to book ten hours. Anyone who is a member of a group can book on behalf of that group. I want to be able to keep a booking history of both users and groups. So we can say

"2 weeks ago, the "white lions" booked nine and a half hours, but the week before, they only booked 3", 

and we can also say

"Dave has made 75% of the bookings for the white lions in the past two weeks"

Ideally, I want to be able to create graphs with this history, too.

What is the best way to do this? I was thinking that for group I would put an array of weeks sorted by ISO week number and year as array keys, and each key would give a value of how many hours the groups booked that week. The same could go for user. But this generates a longtext column (array is serialized) in both user andgroup tables, which will be fetched a lot and, on the whole, used very little.

I'm using MySQL.

What are your thoughts?

EDIT: I wrote an aggregate function and I just want to make sure that this is the kind of thing we're talking about:

function getGroupHoursPerWeek( Group $group , $sometime_during_week = null ) {
global $em;
if( !is_null( $sometime_during_week )) {
    $year = date("o", $sometime_during_week );
    $week_number = date("W", $sometime_during_week );
} else {
    $year = date("o");
    $week_number = date("W");
}
$week_start = strtotime( $year . "W" . $week_number );
$one_week_seconds = 3600 * 24 * 7;
$week_end = $week_start + $one_week_seconds;
$total_time = 0;

$query = $em->createQuery("SELECT e.start_time, e.end_time FROM SSMURBS\Entry e 
                            JOIN e.group g 
                            WHERE g.id = {$group->id} 
                            AND e.start_time > $week_start 
                            AND e.end_time < $week_end
                            AND e.status != " . ENTRY_STATUS_REJECTED );
$entry_times = $query->getScalarResult();

foreach( $entry_times as $entry_time ) {
    $total_time += ( $entry_time["end_time"] - $entry_time["start_time"] );
}
return $total_time / 3600;
}

---

Why deal with a serialized array?

Sounds like you need more entities than just users and groups.

I'd do something like:

• Users
• Groups
• Rooms
• Bookings

A booking would have associations with all three other entities, along with datetime & duration, or something. It's not clear if your users can belong to more than one group (or change groups) -- if they can, then each booking would require both a user and group set. If not, you can get away with just the user, since that will tell you what group is involved.

Then you have all the data you need to generate whatever reports you want.