Mysql custom sequence generator ( like oracle)

Question

I want to have two auto_increment column's per table, but mysql allows only one auto_increment columns. So, I tried replicating the oracle sequence using my own table.

Here is the schema.

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)
);

So, the logical_id_seq table is used as a sequence generator.

creating new entity:

1. Insert new entry into logical_id_seq.
2. Read the last_insert_id() from logical_id_seq.
3. Use the above value to insert a new row in the table.

Let me give you little more context on logical_id and physical_id. I want to design a time travel database, meaning I want the database state given any timestamp(now or past). So, I am having version_start_date and version_end_date.

Can you please tell me if there are side effects to my method of sequence generator.

Answer 1

Depending on your table type (IIRC it's MyISAM and BDB), there's a clever trick you can use with autoincrement.

create table component_core ( 
    component_id int auto_increment, 
    primary key(component_id) 
); 

create table component_history ( 
    component_id int not null, 
    version_id int auto_increment, 
    data varchar(20), 
    version_start_date datetime not null, 
    version_end_date datetime not null, 
    primary key(component_id,version_id) 
); 

Insert into component_core when creating a brand new component, then use that autoincremented ID as the component_id when inserting into component_history and the version_id autoincrement field there will number from 1 upwards for each different component_id. When inserting a change to a component_history, use the original component_id, but allow the version_id to autoincrement normally. In this case, the generated value for the auto_increment column is calculated as MAX(auto_increment_column) + 1 WHERE component_id=given-component_id.

Insert a new component_core
Retrieve the last autoincremented value from component_core (1)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id
Insert a new component_history using the same component_id
Insert a new component_core
Retrieve the last autoincremented value from component_core (2)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id

Tables will now contain

component_core

component_id
1
2

component_history

component_id version_id
1            1
1            2
1            3
2            1
2            2

Not sure if the technique is of any help, especially as it is restricted to specific table types (I believe it works in MyISAM, but you lose out on transactional control with MyISAM)

EDIT

Reference: http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Answer 2

So, I want to keep track of modifications to the rows (...)

I suggest to have a look at Hibernate Envers before to implement your own solution:

The Envers project aims to enable easy auditing/versioning of persistent classes. All that you have to do is annotate your persistent class or some of its properties, that you want to audit, with @Audited. For each audited entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.