Can "auto_increment" on "sub_groups" be enforced at a database level?

Question

In Rails, I have the following

class Token < ActiveRecord
  belongs_to :grid
  attr_accessible :turn_order
end

When you insert a new token, turn_order should auto-increment. HOWEVER, it should only auto-increment for tokens belonging to the same grid.

So, take 4 tokens for example:
Token_1 belongs to Grid_1, turn_order should be 1 upon insert.
Token_2 belongs to Grid_2, turn_Order should be 1 upon insert.

If I insert Token_3 to Grid_1, turn_order should be 2 upon insert.
If I insert Token_4 to Grid_2, turn_order should be 2 upon insert.

There is an additional constraint, imagine I execute @Token_3.turn_order = 1, now @Token_1 must automatically set its turn_order to 2, because within these "sub-groups" there can be no turn_order collision.

I know MySQL has auto_increment, I was wondering if there is any logic that can be applied at the DB level to enforce a constraint such as this. Basically auto_incrementing within sub-groups of a query, those sub-groups being based on a foreign key.

Is this something that can be handled at a DB level, or should I just strive for implementing rock-solid constraints at the application layer?

Answer 1

My opinion: Rock-solid constraints at the app level. You may get it to work in SQL -- I've seen some people do some pretty amazing stuff. A lot of SQL logic used to be squirreled away in triggers, but I don't see much of that lately.

This smells more like business logic and you absolutely can get it done in Ruby without wrapping yourself around a tree. And... people will be able to see the tests and read the code.

Answer 2

If i understood your question properly then you could use one of the following two methods (innodb vs myisam). Personally, I'd take the innodb road as i'm a fan of clustered indexes which myisam doesnt support and I prefer performance over how many lines of code I need to type, but the decision is yours...

http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html

http://stackoverflow.com/questions/3534597/rewriting-mysql-select-to-reduce-time-and-writing-tmp-to-disk/3535735#3535735

full sql script here : http://pastie.org/1259734

innodb implementation (recommended)

-- TABLES

drop table if exists grid;
create table grid
(
grid_id int unsigned not null auto_increment primary key,
name varchar(255) not null,
next_token_id int unsigned not null default 0
)
engine = innodb;

drop table if exists grid_token;
create table grid_token
(
grid_id int unsigned not null,
token_id int unsigned not null,
name varchar(255) not null,
primary key (grid_id, token_id) -- note clustered PK order (innodb only)
)
engine = innodb;

-- TRIGGERS

delimiter #

create trigger grid_token_before_ins_trig before insert on grid_token
for each row
begin

declare tid int unsigned default 0;

  select next_token_id + 1 into tid from grid where grid_id = new.grid_id;

  set new.token_id = tid;

  update grid set next_token_id = tid where grid_id = new.grid_id;

end#

delimiter ;

-- TEST DATA

insert into grid (name) values ('g1'),('g2'),('g3');

insert into grid_token (grid_id, name) values
(1,'g1 t1'),(1,'g1 t2'),(1,'g1 t3'),
(2,'g2 t1'),
(3,'g3 t1'),(3,'g3 t2');

select * from grid;
select * from grid_token;

myisam implementation (not recommended)

-- TABLES

drop table if exists grid;
create table grid
(
grid_id int unsigned not null auto_increment primary key,
name varchar(255) not null
)
engine = myisam;

drop table if exists grid_token;
create table grid_token
(
grid_id int unsigned not null,
token_id int unsigned not null auto_increment,
name varchar(255) not null,
primary key (grid_id, token_id) -- non clustered PK 
)
engine = myisam;

-- TEST DATA

insert into grid (name) values ('g1'),('g2'),('g3');

insert into grid_token (grid_id, name) values
(1,'g1 t1'),(1,'g1 t2'),(1,'g1 t3'),
(2,'g2 t1'),
(3,'g3 t1'),(3,'g3 t2');

select * from grid;
select * from grid_token;

Answer 3

This to me sounds like something you'd want to handle in an after_save method or in an observer. If the model itself doesn't need to be aware of when or how something increments then I'd stick the business logic in the observer. This approach will make the incrementing logic more expressive to other developers and database agnostic.