I'd start off by trying to fix all the predetermined information in the model itself including
- dates/venues
- structure (ie group/knockout stages)
- rules (ie points scoring, tie-break rules)
Some of this information will be data in tables, some will be codified logic in views.
Something like this perhaps:
- team(team_id, group_code enum('A', 'B', 'C', 'D'), name)
- match(match_id, kickoff_at)
- group_match(match_id, team_id_home, team_id_away, group_code)
- knockout_match(match_id, knockout_code enum('Q1', 'Q2', 'Q3', 'Q4', 'S1', 'S2', 'F')
- result(match_id, score_home, score_away)
Information such which teams play in Q1 never needs to be stored directly because it can be calculated from the group stage results. The only changes to make as the tournament progresses are inserts into the result
table.
You can change your post_order
column to a float
and update the value with a calculation using the post_order
values from the posts you want to end up between.
Example:
PostID, PostOrder
1 1
2 2
3 3
If you want to move PostID = 3 to be sorted between 1 and 2 it would be
PostID, PostOrder
1 1
3 1.5
2 2
Here is a SQL Fiddle with some code that moves the posts using a stored procedure where you pass in the PostID
of the post to be moved and PostID
of the post you want to end up behind.
SQL Fiddle
MySQL 5.5.30 Schema Setup:
create table Post
(
PostID int primary key,
PostOrder float unsigned not null unique,
check (PostOrder > 0)
);
insert into Post(PostID, PostOrder) values (1, 1);
insert into Post(PostID, PostOrder) values (2, 2);
insert into Post(PostID, PostOrder) values (3, 3);
insert into Post(PostID, PostOrder) values (4, 4);
insert into Post(PostID, PostOrder) values (5, 5);
insert into Post(PostID, PostOrder) values (6, 6);
insert into Post(PostID, PostOrder) values (7, 7);
insert into Post(PostID, PostOrder) values (8, 8);
insert into Post(PostID, PostOrder) values (9, 9);
insert into Post(PostID, PostOrder) values (10, 10);
//
create procedure MovePost(MovePostID int, AfterPostID int)
begin
declare AfterPostOrder float;
declare NextPostOrder float;
set AfterPostOrder = (select PostOrder
from Post
where PostID = AfterPostID);
if AfterPostOrder is null then
-- Move first
set AfterPostOrder = 0;
end if;
set NextPostOrder = (select min(PostOrder)
from Post
where PostOrder > AfterPostOrder);
if NextPostOrder is null then
-- Move last
set NextPostOrder = (select max(PostOrder) + 2
from Post);
end if;
update Post
set PostOrder = (AfterPostOrder + NextPostOrder) / 2
where PostID = MovePostID;
end
Query 1:
call MovePost (7, 3); -- Move 7 after 3
call MovePost (8, 3); -- Move 8 after 3
call MovePost (9, null); -- Move 9 first
call MovePost (2, 10); -- Move 2 after 10
select *
from Post
order by PostOrder
Results:
| POSTID | POSTORDER |
----------------------
| 9 | 0.5 |
| 1 | 1 |
| 3 | 3 |
| 8 | 3.25 |
| 7 | 3.5 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 10 | 10 |
| 2 | 11 |
Best Answer
LOAD DATA ... INTO staging ...;
It has VARCHARs in place of INTs, etc. It also has some uniqueid
(perhapsauto_increment
) for step 3.Copy "bad" rows to
debtor_err
in one pass:INSERT INTO debtor_err SELECT ... WHERE LENGTH(..) > .. OR NOT EXISTS( SELECT * ... ) -- FK check OR ... > 12
Copy "good" rows to
debtor
table -- Use JOIN to see which ones are goodINSERT INTO debtor SELECT ... -- where nec, convert from VARCHAR FROM staging s LEFT JOIN debtor_err e ON s.id = e.id -- suitable UNIQUE key WHERE e.id IS NULL -- to get the non-error rows
DELETE FROM staging;
-- Reset for next time. (UsingDELETE
instead ofTRUNCATE
may avoidid
not working 'right' the second time around.)Suggestion: Use MyISAM for
staging
; InnoDB for permanent tables.