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

What is the best way for storing invoices in one table. Primary key is autogenerated but invoice number st开发者_运维技巧arts from number 1 on each year.

Is this OK?

invoces
------------------------------------------------
`id`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
`invoice_number`  int NOT NULL ,
`year`  year NOT NULL ,
etc...
PRIMARY KEY (`id`)

Invoice number column is starting from 1 on year start.

Thanks :)

EDIT: Problem: How to determin when a year ends/starts ?

Idea: Create another table for years. When time comes close year (set column for example for 2010 set opened=0) and add a row for new year (for example set field opened=1 for 2011).

New schema:

invoces
------------------------------------------------
`id`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
`invoice_number`  int NOT NULL ,
`fk_year_id`  year NOT NULL ,
etc...
PRIMARY KEY (`id`)

invoces_year
------------------------------------------------
`id`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
`year`  year NOT NULL ,
`opened`  tinyint NOT NULL
PRIMARY KEY (`id`)

Yup. That's fine. I do it like that. Having UNIQUE key on (year, invoice_number) will help avoid accidental errors.

One more thing you should consider is: if the invoice is ever 'cancelled' or 'deleted', will you want to allow for 'recycling' the number used or not. If yes, then the setup can get a bit more complicated.

You could make your primary key a compound field of invoice_number and year.

This removes the need for the autoincrementing ID.

Another way of performing this would be to create the table as follows:

CREATE TABLE invoices(
invoiceyear int,
invoiceid int AUTO_INCREMENT ,
amount decimal,
PRIMARY KEY ( invoiceyear, invoiceid )
)

The result of this is that each year a new invoiceid is reset automatically. The result would be something like this:

invoiceyear invoiceid   amount
2011    1   1
2011    2   1
2011    3   1
2010    1   1
2010    2   1
2011    4   1

However, it should be noted that if you delete an invoice and the invoice has the latest number for that year then this number will be reused. However, if you remove invoice 3 above this will not be reused.

Another idea is to use a compound key and an auto incrementing invoice number.

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 |
+------+----------------+

The downside (and I personally think this is big) is that there is no easy way of porting that behaviour to other databases.

Updated As pointed out in the comments, the "reset behaviour" only works with MyISAM tables. This is an even bigger downside because you won't be able to use InnoDB (which you should for this kind of data).