Mysql auto increment alternatives

Question

Hi,

I have the below table.

create table mytable(
physical_id int auto_increment,
logical_id int,
data varchar(20),
version_start_date datetime,
version_end_date datetime,
primary key(physical_id),
unique key(logical_id,version_start_date, version_end_date)
);

The idea behind the schema is, I want to keep track of modification to every row and find the valid row on any particular date by checking the version_start_date and version_end_date. I want my logical id to be auto_increment, but mysql allows only one id to be auto_increment.

So, I want to set logical_id to physical_id, when creating a new row. I am able to do it using the trigger.

delimiter $$
create trigger myTrigger before insert on mytable for each row begin set 
new.logical_id = (select auto_increment from information_schema.tables 
where table_schema = database() and table_name = 'mytable') ; end$$
delimiter ;

Few other options I checked out are, http://feedblog.org/2007/06/20/portable-sequence-generation-with-mysql/ and http://www.redhat.com/docs/en-US/JBoss_Hibernate/3.2.4.sp01.cp03/html/Reference_Guide/Native_SQL-Custom_SQL_for_create_update_and_delete.html

The problem with these approaches is, I have to create a new sequence table and keep inserting a record into that table.

Is there a better alternative?

Thank you
Bala

-- Update

I am not sure why @tpdi, why I need parent, when I can just emulate this with below table.

create table logical_id_seq (
 logical_id int auto_increment,
primary key(logical_id)
);

create table mytable (
physical_id int auto_increment,
logical_id int not null references parent(logical_id),
data varchar(20),
version_start_date datetime not null,
version_end_date datetime not null,
primary key(physical_id),
foreign key (logical_id) references logical_id_seq(logical_id),
unique key (logical_id,version_start_date,version_end_date)
);

Answer 1

Why wouldn't you want to have a table with the current version, and then an additional table for a history? Then, you use an insert into the current version table to generate your ID, and only query the history table when you actually need the history information.

Answer 2

This is why I prefer Oracle/PostgreSQL sequences to MySQL's auto_increment and SQL Server's IDENTITY - you'd be able to define multiple sequences for this.

Creating a separate table solely for a column in order to use auto_increment for an additional auto_increment is the best solution I can think of, accessed via trigger or stored procedure.

Answer 3

Now I understand your issue :). Changing my answer.

You will need to use some trigger so that whenever some row is edited logical id is incremented by 1 finding max of logical id available in table.

Answer 4

create table parent (
  logical_id int auto_increment,
  physical_id int null references mytable(id)
);


create table mytable(
  physical_id int auto_increment,
  logical_id not null references parent(logical_id),
  data varchar(20),
  version_start_date datetime,
  version_end_date datetime,
  primary key(physical_id)
);

To insert a new instance of an existing record, insert into mytable ...., then capture new mytable.id, and update parent's physical_id with it (possibly in a trigger).

Now, to look up the current record, look up the logical id in parent, and use parent.physical_id to join to the correct record in "mytable"

Parent points to the current valid record (of all records) in "mytable".

To find all instances of a logical record, use the logical_id in "mytable".

To insert a wholly new record, first insert into parent to get the new logical_id, then insert the data in "mytable"; this is why we allow parent.physical_id to be nullable.

As to the OP's update: yes, you can "emulate" by just "stealing" a sequence, but that doesn't model what's really going on. "Stealing" the sequence is just an implementation detail; having two tables as I've shown above makes explicit what's really going on, namely: I have a current state (which parent points to) in "mytable", and 0, 1 or many prior states in "mytable". It's a cleaner separation of concerns, a table that tells you "what's current for any id", and a table that tells you "the data of that current state, and the data of prior states".