Is there a reason/benefit I am not aware of for this kind setup?

Question

I now manage a MySQL database that was designed and created by another.

I am confused about a certain setup that the original developer created..

He did not use auto increments for table IDs. Instead, he created a table called system_sequences which has two columns:

system_sequences.Table_Name 
system_sequences.Next_Value

Here is an example of a table and how it currently inserts into the table (try my best):

there is a table called customer with an unique ID column.

If I want to insert into customer, currently I have to do the following:

SELECT system_sequences.Next_Value 
FROM system_sequences 
WHERE system_sequences.Table_Name = 'customer'

save the next_value into a variable and use it as the next ID for customer

then I have to:

UPDATE system_sequences 
SET Next_Value = Next_Value + 1 
WHERE system_sequences.Table_Name = 'customer'

I did not design this, I do not like it in any way shape or form ( I actually hate it ) and I want to convert everything carefully to auto_increment

But is there a reason for this or a benefit that I am not aware of? I have never seen anything like it before. And there is no documentation for this database that explains anything.

Answer 1

I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for accounting auditing.

Answer 2

The original designer probably had some kind over quick overview of DB resources in mind.

I know Oracle supports a database object called a SEQUENCE that that by just summoning the next value, the auto_increment is performed with the sequence object.

You create it like this:

CREATE SEQUENCE eseq INCREMENT BY 1;

The first reference to eseq.nextval returns 1.

Again, just call eseq.nextval in your trigger and you are assured a safe number to use each time. MySQL has nothing like this. The original designer may have had this in mind and simply emulated it in light of MySQL not having database objects like an Oracle Sequence.

I can also assume that there is no mechanism or infrastructure for collecting and reusing ids that have been deleted, thus leaving gaps in ids.

Looking at the system resource spec you mentioned, you could guess at how many rows were in a table instead of running SELECT COUNT(*) FROM customers;.

If I had to guess why this was done, my guess would be that the designer did not know about or trust in MySQL having an INFORMATION_SCHEMA database. MySQL has had INFORMATION_SCHEMA database since the inception of MySQL 5.0. All versions prior to MySQL 5.0 do not have an INFORMATION_SCHEMA database.