database design - Schema/Strategy and queries for ranking system -

i've got following schema in db:

| items | create table `items` (   `id` int(11) not null auto_increment,   `title` varchar(255) collate utf8_unicode_ci default null,   `description` text collate utf8_unicode_ci,   `created_at` datetime default null,   `updated_at` datetime default null,   primary key (`id`) ) engine=innodb auto_increment=6 default charset=utf8 collate=utf8_unicode_ci |  | rankings | create table `rankings` (   `id` int(11) not null auto_increment,   `user_id` int(11) default null,   `item_id` int(11) default null,   `rank` int(11) default null,   `created_at` datetime default null,   `updated_at` datetime default null,   primary key (`id`) ) engine=innodb auto_increment=10 default charset=utf8 collate=utf8_unicode_ci | 

i want users able add items , rank them against each other. administrators in system able view list of items sorted average rank. i'm determining average rank across users query this:

select avg(r.rank) rank, i.* rankings r left outer join items on r.item_id = i.id group i.id order rank asc; 

what i'm struggling how craft query or have insert strategy when new item added , has not been ranked users yet. 2 viable strategies see are:

1) construct query sorted list across users such unranked items still accounted perhaps added end of list each user sorted created date

2) when new item added, add row in rankings table each user new item, placing last in current rankings

thoughts?

i'm not mysql user, 1 of 2 syntaxes work you. use ifnull in avg calculation.

select avg(ifnull(r.rank,0)) rank, i.*    rankings r left outer join items on r.item_id = i.id group i.id order rank asc; 

or, if mysql doesn't allow nesting of functions that:

select avg(s.rank) avg_rank, s.*   ( select ifnull(r.rank,0) rank, i.*             rankings r left outer join items on r.item_id = i.id          group i.id ) s order avg_rank asc; 

you should put in foreign key relationship between rankings.item_id , items.id in table definitions on delete cascade.