I wish to have two auto_increment column's per table, but mysql enables just one auto_increment posts. So, I attempted replicating the oracle sequence using my very own table.
This is actually 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 can be used like a sequence generator.
creating new entity:
- Place new entry into logical_id_seq.
- Browse the last_place_id() from logical_id_seq.
- Make use of the above value to place a brand new row within the table.
Allow me to provide you with a bit more context on logical_id and physical_id. I wish to design a period travel database, meaning I would like the database condition given any timestamp(now or past). So, I'm getting version_start_date and version_finish_date.
Are you able to please let me know if you will find unwanted effects to my approach to sequence generator.
Depending you are cooking type (IIRC it's MyISAM and BDB), there is a clever trick you should 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) );
Place into component_core when designing a completely new component, then use that autoincremented ID because the component_id when placing into component_background and the version_id autoincrement area there'll number from 1 upwards for every different component_id. When placing a big change to some component_history, make use of the original component_id, but permit the version_id to autoincrement normally. Within this situation, the produced value for that 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 contain
component_id 1 2
component_id version_id 1 1 1 2 1 3 2 1 2 2
Unsure when the way is associated with a help, especially because it is limited to specific table types (In my opinion it really works in MyISAM, however, you miss out on transactional control with MyISAM)
So, I wish to keep an eye on modifications towards the rows (...)
I would recommend to take a look at Hibernate Envers before to implement your personal solution:
The Envers project aims make it possible for easy auditing/versioning of persistent classes. All you need to do is annotate your persistent class or a number of its qualities, that you would like to audit, with
@Audited. For every audited entity, a table is going to be produced, that will contain the good reputation for changes designed to the entity. After that you can retrieve and query historic data with little effort.