Mysql(db design) - storing invoices that starts from 1 each financial year

[rchawdhari]

create table invoices(
   year           int not null
  ,invoice_number int not null auto_increment
  ,primary key(year, invoice_number)
);

insert into invoices(year) values(2009);
insert into invoices(year) values(2009);
insert into invoices(year) values(2009);

insert into invoices(year) values(2010);
insert into invoices(year) values(2010);
insert into invoices(year) values(2010);

select * 
  from invoices;

+------+----------------+
| year | invoice_number |
+------+----------------+
| 2009 |              1 |
| 2009 |              2 |
| 2009 |              3 |
| 2010 |              1 |
| 2010 |              2 |
| 2010 |              3 |

Invoice should be auto generated from every year of 1 april I have created such tables but it will generate from 1 jan , I want to start from 1 april

[cereal]

If the invoice numbering goes, for example, from 2009/04/01 to 2010/03/31 then the column year should take both years or the full date string, something like:

2009-2010

Or:

20090401-20100331

Then it can work as you desire:

create table invoices(
    years char(9) not null,
    invoice_number int unsigned not null auto_increment,
    primary key(years, invoice_number)
) engine = myisam;

insert into invoices (years) values('2009-2010'), ('2009-2010'), ('2009-2010');
insert into invoices (years) values('2010-2011'), ('2010-2011');

select * from invoices;
+-----------+----------------+
| years     | invoice_number |
+-----------+----------------+
| 2009-2010 |              1 |
| 2009-2010 |              2 |
| 2009-2010 |              3 |
| 2010-2011 |              1 |
| 2010-2011 |              2 |
+-----------+----------------+
5 rows in set (0.00 sec)

Note: this works on MyISAM engine, but it will not work on InnoDB because this engine requires the auto_increment column in the first position of the primary index key, so:

primary key(invoice_number, years)

To automatically switch the years segments you could use a function, for example this will check if the submitted date is previous of the current 1st of April:

drop function if exists currentyear;
delimiter //
CREATE FUNCTION currentyear(dt datetime)
RETURNS char(9) deterministic
BEGIN
DECLARE result CHAR(9);
CASE WHEN DATEDIFF(dt, CONCAT(YEAR(dt), '-', '04-01')) >= 0 THEN SET result := CONCAT(YEAR(dt), '-', YEAR(DATE_ADD(dt, INTERVAL 1 YEAR)));
ELSE SET result := CONCAT(YEAR(DATE_SUB(dt, INTERVAL 1 YEAR)), '-', YEAR(dt));
END CASE;
RETURN result;
END//
delimiter ;

> select currentyear(now());
+--------------------+
| currentyear(now()) |
+--------------------+
| 2014-2015          |
+--------------------+
1 row in set (0.00 sec)


> select currentyear('2014-03-31 23:59:59');
+------------------------------------+
| currentyear('2014-03-31 23:59:59') |
+------------------------------------+
| 2013-2014                          |
+------------------------------------+
1 row in set (0.00 sec)

And can be used in the inserts:

insert into invoices (years) values(currentyear(now()));

Personally, I would prefer to rely on solution that makes me able to use transactions. Bye!

[rchawdhari]

ThankYou It solves my problem

[vivek.d]

how to set the auto increment invoice_number value from 100
my expected output:

create table invoices(
years char(9) not null,
invoice_number int unsigned not null auto_increment,
primary key(years, invoice_number)
)

select * from invoices;

+-----------+----------------+
| years | invoice_number |
+-----------+----------------+
| 2009-2010 | 100 |
| 2009-2010 | 101 |
| 2009-2010 | 102 |
| 2010-2011 | 100 |
| 2010-2011 | 101|
+-----------+----------------+

Thanks.

[Taywin]

Before anything else, if you want to ask a question, create a new one. Please do not PULL an old thread of someone else to ask a question.

Now, you are talking about starting value of auto increment value? From your script, it could be...

create table invoices(
  years char(9) not null,
  invoice_number int unsigned not null auto_increment=100,
  primary key(years, invoice_number)
)

[pritaeas]

create table invoices
(
    years char(9) not null,
    invoice_number int unsigned not null auto_increment,
    primary key(years, invoice_number)
) AUTO_INCREMENT = 100

[vivek.d]

Thanks I tried your structure but it's not woking.Showing the below error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=100,

[pritaeas]

Did you try Taywin's example?

[Taywin]

Err, I didn't test it out. Sorry :P Also, I am not sure you can have multiple 'primary' for auto_increment. You can have only 1 auto_increment field... Select one...

[cereal]

Hi, I'm not sure this will work, because in this case the auto_increment column needs to be in the first position of the primary key, example with the auto increment is second position, as above:

create table io(
    id int unsigned not null auto_increment,
    cat tinyint unsigned not null,
    msg text,
    primary key(cat, id)
) engine = myisam auto_increment = 100;

Now, it sets the auto_increment:

show create table io;

 CREATE TABLE `io` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat` tinyint(3) unsigned NOT NULL,
  `msg` text,
  PRIMARY KEY (`cat`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100;

But if we try to add something:

insert into io(cat, msg) values(1, 'hello'), (1, 'world');
insert into io(cat, msg) values(2, 'oranges'), (2, 'apples');

Will return:

+----+-----+---------+
| id | cat | msg     |
+----+-----+---------+
|  1 |   1 | hello   |
|  2 |   1 | world   |
|  1 |   2 | oranges |
|  2 |   2 | apples  |
+----+-----+---------+
4 rows in set (0.00 sec)

As you see it doesn't consider the auto_increment setting. Now, if we alter the table to reorder the index, it will start from 100 but it won't group anymore, this is the test:

alter table io modify id int unsigned not null, drop primary key;
alter table io modify id int unsigned not null auto_increment, add primary key(id, cat);

Here show create table io returns:

CREATE TABLE `io` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat` tinyint(3) unsigned NOT NULL,
  `msg` text,
  PRIMARY KEY (`id`,`cat`) -- inverted order
) ENGINE=MyISAM AUTO_INCREMENT=100

And inserting as before:

insert into io(cat, msg) values(1, 'hello'), (1, 'world');
insert into io(cat, msg) values(2, 'oranges'), (2, 'apples');

Returns:

+-----+-----+---------+
| id  | cat | msg     |
+-----+-----+---------+
|   1 |   1 | hello   |
|   2 |   1 | world   |
|   1 |   2 | oranges |
|   2 |   2 | apples  |
| 100 |   1 | hello   |
| 101 |   1 | world   |
| 102 |   2 | oranges |
| 103 |   2 | apples  |
+-----+-----+---------+
8 rows in set (0.00 sec)

As you see, now it starts from 100 but it doesn't group anymore.

[cereal]

A little update to use transactions and InnoDB tables. We could create a table without an auto_increment column and the use transactions to get the max invoice number, for example:

create table invoices(
  years char(9) not null,
  invoice_number int unsigned not null,
  primary key(years, invoice_number)
) engine = innodb;

start transaction;

set @maxid = null;
set @currentyear = currentyear(now());
select @maxid := coalesce(max(invoice_number), 99) + 1 as max from invoices where years = @currentyear;
insert into invoices(years, invoice_number) values(@currentyear, @maxid);

commit;

With coalesce we define the starting number, in case the WHERE condition years = @currentyear is false, then coalesce will return NULL and by default is applied the second argument, which in this case is 100. In case you don't want to start from 100, then replace:

coalesce(max(invoice_number), 99)

With:

coalesce(max(invoice_number), 0)

The result will look like:

 select * from invoices;
+-----------+----------------+
| years     | invoice_number |
+-----------+----------------+
| 2013-2014 |            100 |
| 2013-2014 |            101 |
| 2013-2014 |            102 |
| 2014-2015 |            100 |
| 2014-2015 |            101 |
| 2014-2015 |            102 |
+-----------+----------------+
6 rows in set (0.00 sec)

Here's the sqlfiddle: http://sqlfiddle.com/#!9/096f2/1

And this is a PHP example:

<?php

# $db
require '../connections/pdo.php';

if(array_key_exists('insert', $_GET))
{
    # when receiving the datetime from $_GET,
    # the format needs to be validated
    $date = array_key_exists('date', $_GET) ? $_GET['date'] : date('Y-m-d H:i:s');

    $db->beginTransaction();
    $db->query("SET @maxid = null");

    $stmt = $db->prepare("SET @currentyear = currentyear(?)");
    $stmt->execute(array($date));
    $stmt = null;

    $db->query("SELECT @maxid := coalesce(max(invoice_number), 99) + 1 as max FROM invoices WHERE years = @currentyear");

    $db->query("INSERT INTO invoices(years, invoice_number) VALUES(@currentyear, @maxid)");

    $db->commit();
}

$query = $db->query("SELECT * FROM invoices");
$rows = $query->fetchAll(PDO::FETCH_OBJ);

echo "
<table>
    <thead>
        <tr>
            <th>YEARS</th>
            <th>Invoice Number</th>
        </tr>
    </thead>
    <tbody>";
    foreach($rows as $row)
    {
    echo "
        <tr>
            <td>
                {$row->years}
            </td>
            <td>
                {$row->invoice_number}
            </td>
        </tr>";
    }
echo "
    </tbody>
</table>";

By using a link like:

• http://localhost/script.php?insert
• http://localhost/script.php?insert&date=2014-03-31+12:00:00

will add rows to the invoices table. Bye.